Have you tried a nested IF statement instead?
IF [<<EXP>>] == 'VALUE1' then
If [DATECOLUMN]< Today() AND [DATECOLUMN]> DATETRUNC('year',Today()) then [CALC1] else 0 end
ELSEIF [<<EXP>>] == 'VALUE2' then
If [DATECOLUMN]< Today() AND[DATECOLUMN]> (Today()-30) then [CALC1] else 0 end
Thanks Matthew.. I'll try this and let you know..
No problem. Let us know if this resolves your problem.
Now I am getting an ERROR like "CANNOT USE 'NULL' TYPE IN 'IF' EXPRESSION".
Thanks & Regards,
In both the original and Matthew's (IF) version, you aren't specifying what happens if none of the conditions are met. Tableau adds a NULL in that situation. It sounds like your database (you didn't mention which one) is rejecting that, which would be a bug in Tableau.
Anyway, you should be able to work around it if you make that explicit. Just add
before the END in your original CASE statement.
Doh! Should've noticed that. Thank Dan.
Thanks for your response.
But I tried it, in the initial stage itself ..
It comes the error "CANNOT MIX AGGREGATE AND NON AGGREGATE COMPARISONS OR RESULTS IN 'IF' EXPRESSION".
If [DATECOLUMN]< Today() AND [DATECOLUMN]> DATETRUNC('year',Today()) then CountD([ABCCOL]) else 0 end
The [DATECOLUMN] is non-aggregate (computed on every row) and the COUNTD is aggregate (computed across the rows).
I'm not precisely sure what you are trying to compute, but my guess is what you should do is:
Replace all your references to [CALC1] with just 1.
Put the resulting calculation on the filter shelf.
Put [CALC1] on the row or column shelf of your viz.
This will give you [CALC1] for those records that satisfy your CASE condition.