Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

wayneantinore-a
New Contributor III

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

Re: AGGR with Above to get previous month value

Try this

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

5 Replies
MVP
MVP

Re: AGGR with Above to get previous month value

I suggest using a monthfield field to do this

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

wayneantinore-a
New Contributor III

Re: AGGR with Above to get previous month value

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

MVP
MVP

Re: AGGR with Above to get previous month value

Try this

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

wayneantinore-a
New Contributor III

Re: AGGR with Above to get previous month value

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!

MVP
MVP

Re: AGGR with Above to get previous month value

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