3 Replies Latest reply on Jan 19, 2017 1:56 PM by Joe Oppelt

Aggregated/Non Aggregated Date Calculation

I have below formula to calculate Latest 4 week Sales. 'Week End Date' is weekly data not aggregated and 'Min of Dates' is a Min function around a group of dates to return the lowest date so it's aggregated.

How do I keep the below formula intact but solve the aggregate non aggregate issue? Thanks for the help.

IF [Week End Date]<=[Min of Dates] and

datediff('week',[Week End Date],[Min of Dates])<=3

then

sum([Selected Measure])

End

• 1. Re: Aggregated/Non Aggregated Date Calculation

IF ATTR([Week End Date])<=[Min of Dates] and

datediff('week',ATTR([Week End Date]),[Min of Dates])<=3

then

sum([Selected Measure])

End

• 2. Re: Aggregated/Non Aggregated Date Calculation

That made it a valid formula but returned Null. I'm guessing making date an Attr effects the calculations?

• 3. Re: Aggregated/Non Aggregated Date Calculation

Usually wrapping the non-aggregate field in an aggregating function -- ATTR(), SUM(), MIN(), AVG(), etc., -- fixes the mismatch.

And ATTR() is my first go-to fix.

But yes, this is a date, so try MIN() instead.