I'm new to alt hierarchies, so looking for some advice from the trailblazers!
I'm trying an approach utilising alternative hierarchies in PA 2,0 to calculate stock balances. The goal is to store the movements and calculate the balance in any particular week.
The approach is as follows:
- We have a stock cube that contains raw stock movements in the measure "CSOH Movement".
- Create an alt hierarchy in the Weeks dimension called "FY_Balances" which contains BAL consolidations and an opening balance element for each year (see screenshot).
- Create a measure called "CSOH Quantity" in the stock cube that will contain the balances.
- Write a rule using the new CellValueN function to read the BAL consolidation for the current week from the equivalent BAL element in the "FY_Balances" hierarchy (see below)
Here's the Primary hiearchy:
Here's the "FY_Balances" alt hierarchy:
(Note: the consolidated elements have all but the last child with zero weight to make sure the balance shows at every intersection)
Here's the rule I've written:
Code: Select all
['CSOH Quantity'] = N:
CellValueN('Stock Cube',
'Week':'FY_Balances':'BAL ' | !Week,
'Stock Cube Measure':'CSOH Movement');
As you can see, it's simply replicating the movement value, not fetching the balance from the alt hierarchy. When I trace the rule, I get an unfamiliar notation for the alt hierarchy. In the element list the following string appears:
Code: Select all
^FW201828^FY_Balances:BAL FW201828