2 Replies Latest reply: Feb 14, 2014 2:33 AM by jagan mohan rao appala RSS

    Compare this sum of sales in this month with the same month last year

      I would like to compare the SALES_VALUE where the End_Date is selected from a list box (e.g. 31-12-2013) with the same month last year (e.g. 31-12-2012).  I tried using the following set analysis and it doesn't work.  Appreciate your time and effort to advise on how to make this work.

       

      sum({$<END_DATE={$(=YEAR(END_DATE)-1 & '-' & =MONTH(END_DATE) & '-' & =DAY(END_DATE))}>} SALES_VALUE)

        • Re: Compare this sum of sales in this month with the same month last year
          Hein Schultz

          Please try this for Year to date

          Sum({$<D.Year={'$(vLastYear)'},D.Month={"=$(vMaxMonth)"},D.YearMonth=>}SALES_VALUE)

           

          or this for single Month vs same Month previous year

          Sum({$<D.Year={'$(vLastYear)'},D.Month={$(=max(D.Month))},D.YearMonth=>}SALES_VALUE)

           

          Where

          vLastYear = Max(D.Year) - 1

          vMaxMonth = right(Max(D.YearMonth),2)

           

          D.YearMonth excluded as it is the selection

          • Re: Compare this sum of sales in this month with the same month last year
            jagan mohan rao appala

            Hi,

             

            Try this expressions

             

            For selected month:

            Sum({$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName)))<=$(=MonthEnd(Max(DateDimensionName)))'}>}SALES_VALUE)

             

            For previous year same month:

            Sum({$<YearDimensionName=, MonthDimensionName= QuarterDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -12))<=$(=MonthEnd(Max(DateDimensionName), -12))'}>}SALES_VALUE)

             

            Replace YearDimensionName=, MonthDimensionName= QuarterDimensionName=,DateDimensionName  with your corresponding date dimensions.

             

            Hope this helps you.

             

            Regards,

            Jagan.