Reading Variable value's attribute in calculated measure

[stephlef]

Dear Experts,

I have a scenario in SAC Planning where planning is done on a Version (for example Version 'B1'), and where a corresponding comparison version should be displayed next to it (for 'B1', for instance, we'd need 'C1' to be displayed). I'm looking for ideas to do that. My question is a little more specific (however, ideas are always welcome).

Of course, we'd like to have this done automatically and not with 2 input-ready variables or filters.

First surprise: in a planning model at least, it is not allowed to create a variable on the Version. That would have been nice.

Alright, so I created a 'Versioning' dimension in my model, and a variable on top of it. This 'Versioning' dimension has also got an attribute, named 'Compare Version' (so for Versioning B1, the attribute has C1). Now, you maybe see what my idea was...

Then, still in the model, I created a calculated Measure to have the Comparison Value. I try to lookup the result using the attribute value of the dimension. Something like that:

CompareValue:

RESULTLOOKUP([Value], [d/Versioning].[p/CompareVersion] = ['VarVersioning'])

Error from the formula editor:

Formula could not be computed: Variable VarVersioning property is not the same as Versioning. Try rewriting the formula.

Well, I tried. But didn't find the trick. I didn't find an option to create variables to read the attributes. I guess what I'm looking for is doable... Any idea?

Thanks a lot!

[TijsVanSteen]

Dear Stephane,

It's not super pretty but if your versions are somewhat fixed a workaround is to hard code the version pairs in a calculated measure using the IF() formula. In the following example there are 4 versions, TD1 to TD4 and it compares: TD1 with TD4, TD2 with TD1, TD3 with TD2 and TD4 with TD3:

The formula for the 'Units Comp' measure is:

IF([d/Version] ="public.TD1" ,LOOKUP([P_SalesUnits],[d/Version]="public.TD4" ,[d/Version] ),IF([d/Version] ="public.TD2",LOOKUP([P_SalesUnits],[d/Version]="public.TD1" ,[d/Version] ),IF([d/Version] ="public.TD3",LOOKUP([P_SalesUnits],[d/Version]="public.TD2" ,[d/Version]),IF([d/Version] ="public.TD4",LOOKUP([P_SalesUnits],[d/Version]="public.TD3" ,[d/Version]),null)))).

Kind regards,

Tijs

[former_member278738]

Hello Stéphane,

were you able to solve this issue?

Thanks

[stephlef]

Hello luis.parra,

Unfortunately not... 2 variables were used.