Previous month sales with aggr

[linoyel]

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

[sunny_talwar · Accepted solution]

Try this:

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

[linoyel]

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]

Yes, I understand!

It's quite interesting

Thanks again for your great help!

[sunny_talwar]

Awesome