LOOKUP(Aggregation([Field]),0) will cause Tableau to return Null when there is no data for a cell, so use that in replace of the Aggregation([Field]) that you'd normally use in your table calcs.
ZN(LOOKUP(Aggregation([Field]),0) if you need 0's instead. Note that putting the ZN() inside the lookup won't do what we want, because there is no data for the ZN() to be applied to until the LOOKUP() statement returns the Null.
Sample workbook is attached.
Hi Jonathan, thanks for your answer but for some reason this doesn't work in my workbook. Instead, I managed to overcome the problem by putting in the formula:
Calculation1 -> if [Parameter1]=[Databasefield1] then [Sales] end
Calculation2 -> if [Parameter2]=[Databasefileld2] then [Calculation1] end
and aggregating Calculation2 from there.
It works but the two problems I'm facing now are:
1). How to efficiently include many ifs (many filters) in one calculated field? - 'elseif' doesn't work because I want to be able to change any filter at any time and it seems that if one elseif is true, then it goes straight to the 'end'.
2). The calculation slowed down the workbook. Would you have any ideas how to improve the speed within these calculations?
1 of 1 people found this helpful
As for why the ZN(LOOKUP([field],0)) didn't work, we'd have to get some sample data to figure out why. There are idiosyncracies to how Tableau pads out data that masters like Joe Mako are still trying to understand.
Here's an alternative calculation, using nested IF's:
IF [Parameter2] = [DatabaseField2] THEN
IF [Parameter1 = [DatabaseField1] THEN [Sales] END
Or, even faster:
IF [Parameter2] = [DatabaseField2] AND [Parameter1] = [DatabaseField1] THEN [Sales] END
At a minimum, using parameters causes a performance impact in Tableau because it can't do as much caching of results for the view from the DB or extract. A recommended alternative is to use dashboards with action filters, where instead of a parameter there's a view with the values and when the user clicks on one of those values the data view is updated.
In addition, the way you are using parameters causes even more impact because the parameter is being evaluated against non-aggregated data. Tableau has to return every row from the DB to test values in the IF statements. If there is any way to perform the IF statements against aggregated data, that will improve performance, sometimes radically.
Thanks Jonathan, your post is very helpful.
I tired to recreate my data but but didn't manage to do so (your solution worked on my fictional data).
I use parameters because when using filters option N/A is displayed for null values and I don't want users to be able to choose this option. I don't use actions because there are many options to choose from and therefore they need to be in dropdown lists.
Could you quickly confirm that there is no way of excluding N/As from filters or creating dropdown actions?