5 Replies Latest reply: Nov 29, 2016 12:16 PM by Sunny Talwar RSS

    Previous month sales with aggr

    Linoy Elias

      Hi all,


      I have a problem with calculating previous month sales.

      My sales formula is:



      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