AGGR with Above to get previous month value

[wayne-a]

Hi I'm using an AGGR with above in order to get a previous month's value.  This mostly works but doesn't work when the year rolls over.  For January I always get a blank value instead of the prior December value.  I've looked at a lot of examples and my expression looks like the ones that do this but I can't seem to get this to work.  Thanks in advance for any help.

Here is the expression for prior month

Aggr(above(count(distinct [Order#])),(Order_Year,(numeric,ascending)),(Order_Month,(numeric,ascending)))

Attached is the table output.  I'd want prior month value for January 2017 to have a value of 124894

[sunny_talwar]

I suggest using a monthfield field to do this

Aggr(Above(Count(DISTINCT [Order#])),(Order_MonthYear,(numeric, ascending)))

[wayne-a]

Hi Sunny, thank you for the reply.  How would you construct the Order_MonthYear?  I tried the following in the load script but it didn't work.  If I put the value in a straight table it looks and sorts correctly.

Date(Order_Date,'YYYYMM') as Order_MonthYear

and also tried

Date(Order_Date,'MMYYYY') as Order_MonthYear

[sunny_talwar · Accepted solution]

Try this

Date(MonthStart(Order_Date), 'YYYYMM') as Order_MonthYear

[wayne-a]

Hi Sunny, that was it.  Thank you very much.  I guess even though I was formatting it to just be year and month it was still considering each individual date so it required the MonthStart to make them all the same.  Thanks again!

[sunny_talwar]

Yes, that is exactly what happens.... Date() is just a formatting function.... for example, a lot of the times people have a timestamp and they assume that using Date() function will remove the time portion of the timestamp. But it doesn't, in order to get rid of it, you need to use Floor() function. Same way to get rid of date, you need MonthStart() or you can use MonthName() function, but then you get a pre-defined formatting....