cancel
Showing results for 
Search instead for 
Did you mean: 

SAC - Calculated values adding up to hierarchy nodes

0 Kudos

Hello

We have a scenario where we are calculating "Amount Per Case" via data action.

Amount Per Case = Amount / Volume

The values marked in green against Child member are calculated as expected which are at month level.

i.e. GL = Child and Date = Jan, Feb, Mar

But when it comes to Quarters & Year level (marked in yellow), calculation is not happening and the values are being added up to hierarchy nodes.

i.e. GL = Parent and Date = 2024, 2024.Q1 etc

We cannot use calculated measures because we need to use the measure value for further calculations in data actions.

Data action code:

MEMBERSET [d/Date] = [d/Version].[p/STARTDATE] TO [d/Version].[p/ENDDATE]

MEMBERSET [d/GLACCOUNT] = BASEMEMBER([d/GLACCOUNT] , "PARENT")

DATA([d/Measures] = "AMOUNTPERCASE") = RESULTLOOKUP([d/Measures]="AMOUNT") / RESULTLOOKUP([d/Measures]="VOLUME")

Can someone pls let me know how to fix this issue.

Thanks
Kalyan N

Accepted Solutions (0)

Answers (2)

Answers (2)

N1kh1l
Active Contributor
0 Kudos

kalyan_nkc

Copying from a calculated measure to a measure in Advanced Formula Data Action is currently not supported but is on the roadmap for 2023.Q4. So till then you have to explicitly code the aggregation

Hope this helps !!

Nikhil

0 Kudos

Hello Nikhil

I am not able to see the image which you have attached. Can you please resend it.

When you say that we need to explicitly code the aggregation, does it mean we need to give all the base level members in RESULTLOOKUP?

Thanks
Kalyan N

N1kh1l
Active Contributor
0 Kudos

kalyan_nkc

Re inserted the image. For aggregating you can just create appropriate scope of data using MEMBERSET and then use RESILTLOOKUP.

Nikhil

N1kh1l
Active Contributor
0 Kudos

kalyan_nkc

This is not an issue and is the expected behavior in SAC. Data is stored at base/leaf member and get aggregated at Parent level. The default aggregation behavior is SUM and can be changed for reporting by adjusting the Exception aggregation setting of the measure along with exception aggregation dimensions. So for reporting you have to use the calculated measure with exception aggregation to get the desired aggregation at parent level.

Now coming to the point that you need the aggregated measure values at parent level for calculation. Currently Data Action only work at leaf level members for both read and write. You have to explicitly read all leaf of the parent in RESULTLOOKUP to get the parent value. Passing a parent node to RESULTLOOKUP() will return nothing. You have to explicitly use Variable members to aggregate the values over time and Accounts and then store the results back to leaf member (another intersection ) after the calculation.

Hope this helps !!

Nikhil

0 Kudos

Hello Nikhil

I agree that it is the expected behavior in SAC but the data action calculation works only for base nodes (Jan, Feb, Mar etc) and not for quarters and year level values is something that we are trying to figure out.

If we are using a calculated measure, we are getting exact values as expected but there is a limitation that we cannot use calculated measure in data actions.

Would like to understand if we can copy the calculated measure values into the measure? So that we can use the measure in data actions for further calculations?

Ex: C1 = Calculated Measure; C2 = Measure

Copy C1 values to C2...

Can you pls share your thought on this?

Thanks much
Kalyan N

0 Kudos

Hello Nikhil

If possible, can you pls provide sample code for the below scenario in your earlier comment? We have tried with variable members to aggregate the values but I do not see the expected result.

"You have to explicitly read all leaf of the parent in RESULTLOOKUP to get the parent value. Passing a parent node to RESULTLOOKUP() will return nothing. You have to explicitly use Variable members to aggregate the values over time and Accounts and then store the results back to leaf member (another intersection ) after the calculation."

Ex: In below table, we are calculating Amount/Volume which gives Rate.

Thanks
Kalyan N

N1kh1l
Active Contributor
0 Kudos

kalyan_nkc

I see that you have already executed a Data Action. Put up the code used and I will try to adjust that. Also I see that the Quantity is non aggregated which is a bit strange. The Amounts are in decimal as the sum of the numbers shown will be 3139. provide the amount numbers in decimal.

Nikhil

0 Kudos

Hello Nikhil

Attached the extract of story from SAC along with expected result column values. Please find the data action code below

MEMBERSET [d/GLACCOUNT] = BASEMEMBER([d/GLACCOUNT] , "Net Sales")
VARIABLEMEMBER #TOTAL OF [d/GLACCOUNT]

DATA([d/Measures] = "AMOUNT", [d/GLACCOUNT] = #TOTAL) = RESULTLOOKUP([d/Measures] = "AMOUNT")
FOREACH [d/GLACCOUNT]
	DATA([d/Measures] = "AMT/VOL") = (RESULTLOOKUP([d/Measures] = "AMOUNT") /
					  RESULTLOOKUP([d/Measures] = "VOLUME"))
ENDFOR

image.png

Thanks
Kalyan N

0 Kudos

Hello Nikhil

Any update on the above pls?

Pls let me know if you need any details.

Thanks
Kalyan N

N1kh1l
Active Contributor
0 Kudos

kalyan_nkc

Ant reason volume is not aggregated over the Accounts. As I had said earlier you would need leaf member for all nodes to store the aggregated amount and rate calculation as you cannot store data on parents in data Action. I saw your code but variable member #TOTAL was never used in calculation. The better way of modelling this would be that you create leaf member for each nodes and maintain them as attributes against the child accounts.

MEMBERSET [d/GLACCOUNT]=BASEMEMBER([d/GLACCOUNT] ,"GSV","GS0","FS0","DS0" )
MEMBERSET [d/Measures]="Amount"
DATA([d/GLACCOUNT]=[d/GLACCOUNT].[p/AGGR])=RESULTLOOKUP() // These will aggregate all leaves and write to attribute members
DATA([d/Measures]="Rate")=RESULTLOOKUP([d/Measures]="Amount")/RESULTLOOKUP([d/Measures]="Volume")

Hope this helps !!

Nikhil