Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wayne-a
Creator
Creator

AGGR with Above to get previous month value

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

1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

5 Replies
sunny_talwar

I suggest using a monthfield field to do this

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

wayne-a
Creator
Creator
Author

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

Try this

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

wayne-a
Creator
Creator
Author

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....