+14For example if I have several metrics corresponding each to an allocation rule, and I want to define the allocation rule to be used for each entity of a company.
Is there a best way than using a SWITCH statement to associate a metric with a dimension.
In my example : SWITCH(Entities.Allocation Rule, “A”, ‘Allocation - A’, “B”, ‘Allocation - B’, ...)
It is not totally optimal because I have to update the formulas each time an allocation rule is added.
Thanks in advance !
Best answer by francois
Hi Camille,
It’s tricky finding a solution without the full picture - but what I would expect would be to avoid using a SWITCH at all in your formula. It seems from your example that the Allocation Rule is a text-based property with a set of metrics (Allocation - A, Allocation - B…) but it really should be a dimension (e.g. Allocation Rule, with items A, B…) and a metric Allocation dimensioned by the Allocation Rule. That way if you need to add a new allocation rule, you have to add a new item to your dimension list Allocation Rule, and this will provide a new line in your Allocation metric.
The SWITCH function is great if you have specific calculations (e.g. x a different driver like headcount or sqm) in which case you’ll have to edit the formula each time you add a new rule anyway. In that case it’s still a good practice to add an Allocation Rule dimension list which will ensure you have thought of all cases.
+13Hi Camille,
It’s tricky finding a solution without the full picture - but what I would expect would be to avoid using a SWITCH at all in your formula. It seems from your example that the Allocation Rule is a text-based property with a set of metrics (Allocation - A, Allocation - B…) but it really should be a dimension (e.g. Allocation Rule, with items A, B…) and a metric Allocation dimensioned by the Allocation Rule. That way if you need to add a new allocation rule, you have to add a new item to your dimension list Allocation Rule, and this will provide a new line in your Allocation metric.
The SWITCH function is great if you have specific calculations (e.g. x a different driver like headcount or sqm) in which case you’ll have to edit the formula each time you add a new rule anyway. In that case it’s still a good practice to add an Allocation Rule dimension list which will ensure you have thought of all cases.
+14Hi François,
Maybe I’m mistaking, but if the Allocation Rule is a dimension, I can’t have a specific formula for each ?
For example Allocation A is Turnover / Total Turnover and Allocation B is Cost / Total Costs
Thanks !
+13Yes, this is the tradeoff. If you want a specific formula for each rule, you’ll have to add this formula bit in the allocation formula.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
