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.
Where is your data coming from ?
Is it easier to create this in the data source ? (or even just put the previous value on the same row and then get Tableau to do the 50%/50% calculation).