1 Reply Latest reply on May 19, 2016 5:28 PM by Chris McClellan

    Calculation that references previous quarter value

    Katherine Carothers

      Hi! I'm having trouble figuring out a calculation and haven't been able to find anything similar on the forums. Details are below. Any help would be greatly appreciated!

       

      Goal: To sum 50% of a calculations value from the previous year/quarter and 50% of the same calculations value from current year/quarter in one new calculation that moves year/quarter to year/quarter and is independent from the window layout and can be used within other calculations (specifically, level of detail calcs).

       

      To simplify, we’ll refer to the original calculation value as “NPS”.

       

      Example:

      FY16 Q1: FY16 Q1 NPS

      FY16 Q2: 50% of FY16 Q1 NPS + 50% of FY16 Q2 NPS

      FY16 Q3: 50% of FY16 Q2 NPS + 50% of FY16 Q3 NPS

       

      *The NPS for first year/quarter in the data set should simply be a reflection of itself as there is no previous year/quarter data.

       

      Given that:

      FY16 Q1 NPS = 100

      FY16 Q2 NPS = 80

      FY16 Q3 NPS = 120

       

      Results in:

      FY16 Q1 New Calc = 100

      FY16 Q2 New Calc = 50+40

      FY16 Q3 New Calc = 40+60

       

      *Note, we use the NPS for previous year/quarter when determining new year/quarter value. The new calculation does not build upon itself; rather, it references the NPS (original calculation) each time.

       

      The new calculation will only be displayed by single year/quarter. Users will never be able to see FY16 Q2 and FY16 Q3 at the same time.