on 03-27-2023 11:42 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
Thanks
Kalyan N
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
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.