I need to do such evil calculation. I cannot imagine an approach which I should apply.
I have this tables:
AGING:
LOAD
Material,
1_30,
31_60,
61_90
From .........;
PLAN:
LOAD
Material,
PL.1_30,
PL.31_60,
PL.61_90
From .......;
SP:
Load
Material,
SP.1_30,
SP.31_60,
SP.61_91
From ........;
Logic is. Substract column 1_30 from table SP in table AGING. It need to be calculated from last group to first.
I do there load on load because i reffer last calculated column in current calculation.
rangemax(61_90+31_60+1_30-[SP.1_30]-AG_Q_61_90_D-AG_Q_31_60_D ,0) as AG_Q_1_30_D
rangemax(61_90+31_60-[SP.1_30]-AG_Q_61_90_D ,0) as AG_Q_31_60_D,
rangemax(61_90-[SP.1_30],0) as AG_Q_61_90_D,
After this I should add data from table Plan to matching categories (1_30 to 1_30 etc.)
AG_Q_1_30_D +PL.1_30 as AG2_Q_1_30_D,
An after this i need to do the same calculation as before considering new columns and substracting [SP.31_60]
rangemax(AG2_Q_61_90_D,31_60+1_30-[SP.31_60]-AG2_Q_61_90_D-AG_Q_31_60_D ,0) as AG_Q_1_30_D
rangemax(AG2_Q_61_90_D,+31_60-[SP.31_60]-AG2_Q_61_90_D ,0) as AG_Q_31_60_D,
rangemax(AG2_Q_61_90_D,-[SP.31_60],0) as AG_Q_61_90_D,
And againg add plan from second group 31_60 to matching group in aging with new name and again repeat this calculation with sales plan (SP) from next group 61_90.
This is quite complicated to do in script, there are a lot of tricki things considering my real data contain 21 aging groups not 3 as this example.
But maybe this is possible to do in front end on table chart using measures. Without complicated transformations in script.
