1 Reply Latest reply: Jul 13, 2014 11:35 PM by jagan mohan rao appala RSS

    Set Analysis Question - Previous Year Sales

      Hi, I have some questions around Set Analysis:

       

      If I have two fields for selection, where current month's sales amount is "ACTUAL_SALES_AMT(JPY)", and there are 3 financial years of records, where Financial Years 2012, 2013 & 2014 are called FY and the current months are called Month(MMM)

       

      I need to present the above selections into 4 aggregated expressions as below:

      1.) Total for the current month

      2.) Total for the current 12months

      3.) Total for the LAST YEAR same month period

      4.) Total for the Previous month

       

      I have expressions as below:

      1.) Total for the current month: Sum(ACTUAL_SALES_AMT(JPY)

      Easy enough

       

      2.) Total for the current 12months:

      Need help, am totally clueless how to handle this

       

      3.) Total for the LAST YEAR same month period:

      (Sum({$<FY={">$(=min(FY))<$(=max(FY))"}>}[ACTUAL_SALES_AMT(JPY)]))

      The formula works by capturing the middle year (2013 only), which meets my current 2014 requirements, but if we select 2012 or 2013, this formula becomes redundant. How can I revise this to dynamically reference to the total sales amount of previous year's same month of the selected current month & FY?

       

      I noticed when the previous year does not have records, it will return same total amt as the current month's, which is wrong, should have returned as zero.

       

      I also tried revising to (Sum({$<FY={(FY)-1}>}[ACTUAL_SALES_AMT(JPY)])) but was completely useless then. Please advice.

       

      4.) Total for the Previous month

      Similar to the above, I just swap the FY with Month(MMM), and same problem as above.

      (Sum({$<Month(MMM)={">$(=min(Month(MMM)))<$(=max(Month(MMM)))"}>}[ACTUAL_SALES_AMT(JPY)])) ,

        • Re: Set Analysis Question - Previous Year Sales
          jagan mohan rao appala

          Hi,

           

          If you have date field try like this

           

          1.) Total for the current month

          =Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName)))<=$(=Date(Max(DateDimensionName)))'}>} [ACTUAL_SALES_AMT(JPY)])

           

          2.) Total for the current 12months

          =Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -11))<=$(=Date(Max(DateDimensionName)))'}>} [ACTUAL_SALES_AMT(JPY)])

          3.) Total for the LAST YEAR same month period

          =Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -12))<=$(=MonthEnd(Max(DateDimensionName), -12))'}>} [ACTUAL_SALES_AMT(JPY)])


          4.) Total for the Previous month

          =Sum({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -1))<=$(=MonthEnd(Max(DateDimensionName), -1))'}>} [ACTUAL_SALES_AMT(JPY)])


          Replace YearDimensionName=, MonthDimensionName=, DateDimensionName= field names with your actual field names.


          Note : DateDimensionName and Today() date format should be same, if they are different then above expressions won't work.


          Regards,

          Jagan.