You cannot filter on an aggregated measure (like: Sum(visits)>0), so I'm not sure if this is possible. Someone may have a workaround, which I would be interested to see as well.
Wouldn't that be an equivalent of something like HAVING SUM(Visits) > 0 and then counting number of values returned, if less than 12 do not display.. I would be very much interested in finding a way to do this
1 of 1 people found this helpful
I think you're on the right track with your logic. Here is an example from Superstores where you might want to filter Alabama, Arkansas, and other states that do not have sales in every month of 2013.
Starting with the final field (you'll have to enter this after the below, but it helps to see the goal): States with Missing Values =
[Number of State Values] < [Number of Columns]
Add a calculated field Number of Columns =
When entering this table calc, click on the Default Table Calculation blue text in the upper right corner of the dialog box and select Order Date. After entering a table calc field, I will usually drag it to the Tooltip button to verify it's working. Here you can see that SIZE() == 12 for all rows, even Arkansas which is missing two months.
We'll use another table calc, WINDOW_SUM(), to find Number of State Values =
WINDOW_SUM(IIF(SUM(Sales) > 0, 1, 0))
IIF is just a shorthand way of writing IF SUM(Sales) > 0 THEN 1 ELSE 0. This should also be computed over the Order Date dimension. Again, I added it to the tool tip, and you can see it returns 10 for Arkansas.
Now you can add the Boolean States with Missing Values shown above and add that to the Filter shelf and set it to False. And you'll get the below (you can remove Number of Columns and Number of State Values from the view).
Matt Lutton: "You cannot filter on an aggregate measure (like: Sum(Visits)>0)…" is not accurate. We can always do a filter on a continuous aggregate measure like SUM(Visits) and set that to at least 1, or for example here's a filter on SUM(Sales):
While you are correct that we cannot filter on a discrete regular aggregate measure such as a boolean "SUM(Visits)>0", there are two workarounds. One is to turn that into a continuous value, with a formula like IF SUM(Visits) >0 THEN 1 END, the other is to turn the evaluation into a table calculation like LOOKUP(SUM(Visits),0), because we can add filter table calculation results.
Jim Wahl: On another thread Dan Huff pointed out that the new 8.1 RANK functions ignore the missing values. This leads to another technique for identifying the number of states w/values: WINDOW_MAX(RANK_UNIQUE(SUM([Sales]))), which I would typically write as PREVIOUS_VALUE(WINDOW_MAX(RANK_UNIQUE(SUM([Sales])))) so the calc is evaluated only once in the partition. I'm mentioning it because I think it's more elegant and easier to describe than the embedded filter calc that we've had to use prior to 8.1, however, I'm not sure this will be faster than a PREVIOUS_VALUE(WINDOW_SUM(IIF(SUM(Sales)>0,1,0))), I'll wait until the production 8.1 to test that out.
Jonathan's addition of PREVIOUS_VALUE() around the WINDOW_SUM(IIF(...)) for Number of State Values is a nice optimization that I often forget.
With a table calc function like WINDOW_SUM(), Tableau is calculating this value for each cell. This is not obvious in the example, but if you drag Number of State Values to the Text shelf, you'll see the value 12 in each cell, and Tableau is calculating the SUM of the IIF() results 12 times per row. You can optimize this with PREVIOUS_VALUE(), which returns the previous value in the partition, unless it's the first row in the partition, in which case it executes the function inside the (). Therefore, it's only doing the math once per partition (State in the above example).
With the amount of data in the example, the difference in performance is probably < 100 ms, but it's a nice tweak regardless and helps re-enforce how table calcs work.
I managed to do it, it's great. I will have to use similar solutions all the time...
I'm gonna study those functions now to get my head around it so I don't forget!