I was able to figure out a solution using the following steps -
Create a separate Consolidation Year dimension with a hierarchy such as
Cumulative Year 2019: Contains members 2018 and 2019
Cumulative Year 2020: Contains members Cumulative Year 2019 and 2020
and so on.
Create a Cumulative Cash Flow Cube with this dimension and the Cash Flow metric
Use a rule in
Cumulative Cash Flow Cube to populate the leaf nodes (2018, 2019, etc.) with the Cash Flow values from the original Cash Flow cube
The hierarchy produced the cumulative sums as expected
Use a rule in original Cash Flow Cube to retrieve the Cumulative Cash Flow values from the
Cumulative Cash Flow Cube - this rule needs to use the concatenation of the string 'Cumulative ' and the Year when retrieving the metric values.
This worked like a charm.
Please let me know if you know of an alternative way that is more dynamic - what I wanted was a way to accomplish this using a generic expression in a rule such as
[Cumulative Cash Flow, Current Year] = [Cumulative Cash Flow, Previous Year] + [Cash Flow, Current Year}
which would have been a one-step way to accomplish this.
------------------------------
Shubho Ghosal
------------------------------
Original Message:
Sent: 11-26-2018 04:31 PM
From: Shubho Ghosal
Subject: INSTRUCTIONS NEEDED: Cumulative Sum
Folks, I need to calculate the cumulative sum of a metric. Example below -
Year Cash Flow Cumulative Cash Flow
2018 $100 $100
2019 $200 $300
2020 $500 $800
I don't see a cumulative function in the function stack. Could someone please provide me a technique to achieve this in PAW/TM/1?
Thanks,
Shubho
------------------------------
Shubho Ghosal
------------------------------
#PlanningAnalyticswithWatson