Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
linoybar
Valued Contributor

Previous month sales with aggr

Hi all,

I have a problem with calculating previous month sales.

My sales formula is:

Sum(Aggr(Sales,RowID))

It has an aggr inside because I have few rows with every RowID in my data set and I need to calculate the sum of sales only ONCE per each RowID.


This is my current month formula which works:

Sum({<$(vSetAnalysisExceptTimes), DateNum={">=$(=Num(MonthStart($(vMaxDate))))<=$(=$(vMaxDate))"}>} Aggr(Sales,RowID))


where vSetAnalysisExceptTimes is a variable that clears all Calendar fields.


And this is my previous month formula:

Sum({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>} Aggr(Sales,RowID))


It works only in case I don't choose anything or of I choose both current and previous months, for example November and October, or October and September etc. But it DOES NOT work if I choose only 1 month, for example October. I expect it to show me September sales and I even know the reason it doesn't, I just don't know how to solve it:

the reason is that if the user choose October, the formula does calculate for September (>=1/09/16 <= 30/09/16) but it can't bring those RowIDs that were in September. I guess it still brings RowIDs of October which are never the same as in September, and that's why the result is 0.


I tried to do another set analysis of dates inside the Aggr but it still didn't work...

Sum({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>} Aggr({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>}Sales,RowID))


Any help will be appreciated


Thanks









1 Solution

Accepted Solutions

Re: Previous month sales with aggr

Try this:

Sum({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>} Aggr(Only({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>}Sales),RowID))

5 Replies

Re: Previous month sales with aggr

Try this:

Sum({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>} Aggr(Only({<$(vSetAnalysisExceptTimes), Date={'>=$(=MonthStart($(vMaxDate, -1))<=$(=AddMonths($(vMaxDate),-1))'}>}Sales),RowID))

linoybar
Valued Contributor

Re: Previous month sales with aggr

Yes!!!!!!!!!

How? Why this works with "Only"?

You're a genius

Please explain why it works...

Re: Previous month sales with aggr

Although I have seen (somewhere) that Aggr allows you to use set analysis, but not sure if this is just a recent thing or if this was always the case. In this case (not knowing which version you are using), I think using Only() is equivalent of not using anything, but in order to use the same set analysis that is used in the outside aggregation, you basically had few options:

1) Only

2) Avg

3) Min

4) Max

5) Sum(DISTINCT

In your particular case they will all work, but the main thing to note is that you needed some kind of aggregation function to use set analysis.

Does that make sense?

Best,

Sunny

linoybar
Valued Contributor

Re: Previous month sales with aggr

Yes, I understand!

It's quite interesting

Thanks again for your great help!

Re: Previous month sales with aggr

Awesome