Error in my Tableau calculated field for counting days

0
votes

I am trying to calculate the number of days for a project based on the active_flag. If the active flag is true, the number of days should be calculated from the end_day till today. If the active flag is false, the number of days should be calculated from the end_day till the last observation date (ob_date). Below is my attempt: 

if active_flag = True then datediff('day',[end_date],TODAY()) 
elseif active_flag = False then datediff('day',[end_date], max([ob_date])) end

calc window with errors

I see the errors so I'm looking for suggestions on how to re-write this calc to get the desired result.

1
tableau-apicalculated-field

1 Answers

2
votes

You often came across this error "Cannot Mix aggregate and Non-aggregate". This is because you have called max(aggregate) with "End date" (Non-Aggregate). LOD is helpful in handling situations like this. So the correct code is

if active_flag then datediff('day',[end_date],TODAY()) 
else datediff('day',[end_date], {max([ob_date])}) end

By scripting curly braces before the max calculation, we have forced the Tableau calculation engine to find Maximum of all values in column "Ob_date".

LOD can elevate our calculation capacity. If you are interested checkout this 🔗