Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
linoyel
Specialist
Specialist

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
sunny_talwar

Try this:

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

View solution in original post

5 Replies
sunny_talwar

Try this:

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

linoyel
Specialist
Specialist
Author

Yes!!!!!!!!!

How? Why this works with "Only"?

You're a genius

Please explain why it works...

sunny_talwar

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

linoyel
Specialist
Specialist
Author

Yes, I understand!

It's quite interesting

Thanks again for your great help!

sunny_talwar

Awesome