1 Reply Latest reply: Apr 17, 2012 9:51 AM by paulina6735 RSS

    Period over Period (compare two periods) calculation


      I have a question about period over period calculations. I'm trying to build expressions which will calculate the differencies between periods:

      1. Quarter over Quartet that is difference between eg. Q2 and Q1 of fiscal year (which starts on october)

      2. Month over month which is difference between eg. Mar and Feb

      3. Quarter difference in Half year - if I select H1 I want to have comparison between Q2 and Q1.

      Calculation for Year over Year is very easy and I wanted to rebuild it for first two points but it didn't worked that way. The expression is: =count(id) - count({$<Fiscal_Y={$(#=Only(Fiscal_Y)-1)}>} id)

      Please help me with those, because they're really challenging...

        • Re: Period over Period (compare two periods) calculation

          1. Quarter over Quarter: (need to prepare value Quarter_num which can be 1, 2, 3 or 4) first.

          =count(id) - count({<Quarter={$(='Q'&if(Quarter_num=1,4,Quarter_num-1))}>} id)



          2. Month over month:

          =count(id) - count({<Month={$(=Month(addmonths(min(date),-1)))}>} id)


          3. Half-year selected - calculate difference between quarters:

          =if(Half='H1',count({<Quarter={Q2}>} id)-count({<Quarter={Q1}>} id),count({<Quarter={Q4}>} id)-count({<Quarter={Q3}>} id))