How to create a calculated field by comparing a detail value with aggregated value like a MAX.
If I want to generate a REVENUE field for those transactions happened yesterday, I will use:
if datediff('day', order_date, today()) =1 then [Revenue] else NULL end
If I want to generate a REVENUE field for those transactions happened one before the latest date of all available data, I will use:
if datediff('day', order_date, max_date) =1 then [Revenue] else NULL end
max_date is a calculated field by materializing the maximum date of the same data set (I duplicate in DATA shelf).
But field, the error message is :
Cannot mix aggregate & non-aggregate comparisons or results in IF expression
Any help is highly appreciated.
thank you !!!
Become a Viz Whiz on the Forums!
Support the Community and master Tableau.