Rule Issue when Multiple Hierarchies of the same dimension are used in a PAW View

[tm123]

Hi guys,

I have a Cube with following dimensions:

Scenario
Time Period
Product
Location
Account
Measures

We have a some elements in the Scenario Dimension that are populated with Rules as a combination of Actual data and Forecast Data, so for each scenario, in a COntrol Cube, we keep the First Forecast Month ( for example 2021-03 ) and then we have a Rule as Below:

Code: Select all

[] = IF ( ATTRS ( 'Scenario', !Scenario, 'Scenario Type' ) @= 'Mix' ,
	IF ( ELLEV ( 'Time Period' , !Time Period ), 
		IF ( !Time Period @< ATTRS ( 'Scenario', !Scenario, 'Forecast Start' ) , 
			DB ('My Cube', ATTRS ( 'Scenario', !Scenario, 'Actual Base Scenario' ) , !Time Period , !Product , !Location , !Account, !Measures ) , 
			DB ('My Cube', ATTRS ( 'Scenario', !Scenario, 'Forecast Base Scenario' ) , !Time Period , !Product , !Location , !Account, !Measures )	
		) 
		ConsolidateChildren ( 'Time Period' )
	) ,
	CONTINUE
) ;

The rule above has been working fine for a long time.

Now we added some additional Hierarchies in the Time Dimension such as Years, Quarters, Months

They all share the same Leaf Time Periods.

The rule above works fine until one or more Hierarchies are placed in the View in PAW.

So for example if the User Places Year Hierarchy in the Rows and Month Hierarchy in the Columns, then the Rule above does not work as expected. So it just shows Actual data for all the months ( if my Forecast Start is 2021-03, we want to show Actuals only until 2021-02 and the Forecast for all other Time Periods)

Does anyone have any idea?

[ascheevel]

In your paw cube view with alternate hierarchies in use, what is the level of the base hierarchy of your Time Period dimension? Do you have that at a higher level and are drilling into leaf level elements via the alternate hierarchy?


edit:
Your primary issue is the !Time Period when multiple hierarchies from the same dimension are in use, which !Time Period should the rule be evaluating? You can hierarchy qualify the ELLEV like this: ELLEV('Time Period:Time Period', !Time Period), but your rule won't work right if your base hierarchy is a consolidation and you're using an alternate hierarchy to display the leaves. If your PAW view is only the months and years alternate hierarchies in the rows/columns and the base hierarchy time period is some consolidation in the context filter, one thing to try would be to remove the base hierarchy for Time Period from the cubeview completely after first making your ELLEV rule hierarchy qualified. I believe when an alternate hierarchy is not used in a view, a rule that is testing the ELLEV of that hierarchy will evaluate to level 0 except in cases where a consolidated element in an alternate hierarchy used in the view shares the same name as a consolidated element in the hierarchy your ELLEV is evaluating.