7 Replies Latest reply on May 28, 2013 4:07 PM by Nay Lin Soe

How to dynamically filter on Aggregate Values

Hello all,

I have got transactional data of names and prices. I like to create a filter on the names whose TOTAL PRICE is above a certain value, set by a parameter. In the attached sample, I created Desired - IsAboveMinTotalPrice as SUM([Price]) >= [Min Total Price]. My intention is to put that onto the filter shelf (or even create a set), and select only 'True'. But Tableau wouldn't allow me to put that.

For comparison, I also created IsAboveMinTotalPrice as [Price] >= [Min Total Price]. That one can be put onto the shelf, but obviously the filter is working at the transactional level, not on the aggregate (total) that is desired.

Any suggestion as to how to approach this would be greatly appreciated.

Regards,

Nay

• 1. Re: How to dynamically filter on Aggregate Values

--Shawn

• 2. Re: How to dynamically filter on Aggregate Values

Hi Shawn,

Thanks for having it a go. Unfortunately, I think it's still working at the transactional level. When I set the parameter to \$3, all A, B and C should qualify since all have at least \$3 in total.

But in this solution, only B and C are returned, so it must be on \$1 and \$2 transactional values of A the filter is applied on, instead of the \$3 that is the total

Nay.

• 3. Re: How to dynamically filter on Aggregate Values

Nay,

Is the attached workbook what you are looking for?  I tried to explain myself but if you have questions ill try to fill you on it.  Shawn can probably  explain the window calcs better than I can.

Gerardo

• 4. Re: How to dynamically filter on Aggregate Values

Gerardo, I looked a similar things and got the sort of results you got, which are not what he seems to be looking for. Specifically, when he sets his parameter to \$3 he expects/wants to see all three Names A-C because A has both a \$1 record and a \$2 record, which should sum to \$3 and therefore not get cut out of the picture.

This is one of those Tableau conundrums Jonathan understands. (Jonathan I haven't seen anyone else deal with or explain this issue otherwise I'd have pinged them.)

--Shawn

• 5. Re: How to dynamically filter on Aggregate Values

Thanks Gerardo. As Shawn has observed, the outcome is still not what I am looking for. However, your technique has helped me figure out something that works in a limited sense (attached).

Filter with window_sum works in the way I want. It is limited because you can see that in Filter with window_sum (without year), when I don't wish to include year in the display. Because of the customized table calculation, I have to put year in the level-of-detail box. And the undesired by-product of that is total price is written once for every year for each name. (If I wish to have a viz for total price, each will be repeated twice.)

I am not going to consider this an answer because practically I can't use it for my need. Or you have a smart walk-around? Conundrum is a fitting word.

Nay

• 6. Re: How to dynamically filter on Aggregate Values

Hi,

Going back to the original post, the issue seems to be that the SUM([Price]) >= [Min Total Price] can't go on the Filters Shelf. This calc returns a boolean (discrete) value and Tableau doesn't support putting discrete aggregates on the Filters Shelf. There are two workarounds. One is to use a table calc like LOOKUP(SUM([Price]) >= [Min Total Price],0), that's set up in the "Desired jtd TC" worksheet in the attached, the other is to change the original calc to return a set of continuous values and filter for those, like IF SUM([Price]) >= [Min Total Price]  THEN 1 ELSE 0 END, then filter that for values of 1, that is in the "Desired jtd" in the attached. I'd tend to use the latter, it will be more performant.

Jonathan

• 7. Re: How to dynamically filter on Aggregate Values

Thanks Jonathan. This solves the problem. I too prefer "Desired jtd".

Cheers,

Nay