3 Replies Latest reply on Dec 13, 2016 7:12 AM by Shinichiro Murakami

    How to do QoQ analysis ?

    Kahv Jiv

      Hi, I am working on a report where I have to display profit(as % of total) for all sub-category for all months ( this logic is working fine ) and when user select Quarters then it should be % of difference from previous quarter. i.e. if user select Q2 for fY 2016, it should be Qtr2-Qtr1..similarly for rest of the quarters.

      I have mocked up data.. can anyone pls help me..?

        • 1. Re: How to do QoQ analysis ?
          Shinichiro Murakami

          Hi Kahv,

           

          Here is my approach.

           

          Modify Parameter.

          [Parameter Date]

          date(

          dateparse("yyyyMMdd",str([Param_Year])+

          (if left([Calendar New 2],1)="M"

          then right([Calendar New 2],2)

          else right(str(int(right([Calendar New 2],2))*3+100),2) end)

          +"01"

          ))

          [Date Header]

          if left([Calendar New 2],1)="Q"

          then datename('year',[Order Date])

          +

          "Q"+datename('quarter',[Order Date])

          else

          datename('year',[Order Date])

          +

          "M"+str(datepart('month',[Order Date]))

          end

           

          [Date Filter]

          (if left([Calendar New 2],1)="Q"

          then dateadd('quarter',-1,[Parameter Date])

          else dateadd('month',-1,[Parameter Date])

          END)

          <= [Order Date]

          and

          datetrunc('month',[Order Date])<=[Parameter Date]

          index

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: How to do QoQ analysis ?
            Kahv Jiv

            Hi Shinichiro,

             

            Thanks for your reply !

            can u please help me understand what does

            right(str(int(right([Calendar New 2],2))*3+100),2) end) do in parameter date..? I understand that it will pick qtr values but what is the use of *3 + 100.. can u pls explain. ? Thanks !

            • 3. Re: How to do QoQ analysis ?
              Shinichiro Murakami

              Does this help?

               

              Q1 = 1*3 = 3 = March

              Q2 = 2*3 = 6 = June

              Q3 = 3*3 = 9 = September

              Q4 = 4*3 = 12 = December

               

              Then + 100 makes

              103, 106, 109, 112

               

              right (***,2)

              03,. 06, 09, 12

               

              String and integer in between are what just makes calculation work.

               

               

              Thanks,

              Shin

              1 of 1 people found this helpful