have you tried the ifnull expression to calculate the figure? Not sure if you need the output to be 0 or blank. Sample =ZN(SUM([abs of minute diff])) - LOOKUP(ZN(SUM([abs of minute diff])), -1) Using zn would return 0 if there is data as a factor.
or if null(expr1),0)
Thanks Donna, but "isnull" or "if null" or "zn" only handle Null values not missing values. in below URL:
Richard Leeke's posting attached a clear example to show the slight difference b/w Null and missing values. There is a nice feature in graph to show or hide special values like missing ones, but I don't find any materials to show how to get them controlled in calc.
You can try zn() in the WB I posted if you want, it won't change any blank entries in the view.
I got it!! This worked on your workbook. Create a calculated field: zn(lookup(max([actuals]),0))
Donna, you rock!!!! This is EXACTLY what I was looking for.
Since Shawn highlighted this in his Tableau Forums Digest, I figured it would be worthwhile to put in a couple of extra notes:
If you do just LOOKUP(aggregate,0) you can also use the Format->(your measure)->Pane tab->Special Values section->Text to set a default value for Null's. I tend to use the ZN(LOOKUP(aggregate,0)) technique instead because it keeps all the settings in one place.
If database A has a connector (like ODBC) to database B, you can set up your query in database B as a data source in database A, then do a combined query in database A. A quick search on SQL Server and Teradata shows that there is an ODBC connector. However, given the time (and sometimes IS/IT resources) necessary to set up a connector and get it to work, it's often easier to just use Tableau's data blending.
Anyway to preserve the negative values?
I don't understand the question? The technique that Donna described shouldn't affect negative values at all.
I'm using a OLAP Cube as a source (not sure if that matters), but when I use that formula it throws an error that asks: max is being called with (float), did you mean (float, float)?
So my formula ends up zn(lookup(max([Cash Generated Ratio - Projection],0),0)) and that removes the negative values. Is there something else as the second float? I tried adding the same field again but that does nothing...
Does this make sense?