Hierarchy Value doesn't work, model in SAC to Highest level of Granularity

[Mr-Singh]

I have a calculation where in I want to write the calculation ffor a comp Code, it should do A*1.122. This is working for the lowest granularity but it's not working for High levels of Granularity. Any solutions, thoughts on the logic/Code for the same?

[N1kh1l]

bosingh

SAC does aggregation first and then does the calculation. Please show screenshot of your calculation results to help you better.

Nikhil

[Mr-Singh]

Marked inred is the top level, 101 is the bottom level. Bottom granule works as expected but, Europe doesn't work. Is there any other work around to make it convert

[N1kh1l · Accepted solution]

bosingh

The reason it is not working at parent level is because you are checking the condition in IF statement for plant being 101 which is not true when evaluated at parent level. To get the aggregation right, create another measure and just point it to this calculated measure vol_SQFT and make the exception aggregation as sum. You can do this in story or at the model level similar to previously calculated measure. See example below.

Output: CALC OQ does not aggregate at Europe level but CALC_OQ_AGGR does.

CALC_OQ

CALC_OQ_AGGR: In aggregation dimension you can also select All dimesnison.

Hope this helps !!

Please upvote/accept if this helps

Nikhil

[Mr-Singh · Accepted solution]

Nikhil,

It works as of now. I will do a detailed test and get back if I need further help. Appreciate your response. Thanks.

[Mr-Singh · Accepted solution]

Nikhil - Now I have 2 other hierarchies one with Plant-Region-Sales and another is let's say Plant-y-z for these the formula we used earlier is not working. for my normal Hierarchy with Plant-Sub Div is working. Is there anyway to make it Global and make it work for any Hiearchy which is have Plant as top node and any other su dimension as hierarchy in same Calculation? Thanks for your earlier response.

[N1kh1l · Accepted solution]

bosingh

In Exception Aggregation dimension select all dimensions for which you want the aggregation. You can also select ALL.

Nikhil