In simple terms I want to know the total till the previous rank complicated by the fact that the last number depends on the rankcumulate till previous rank. Hence this is giving rise to Circular references. Let me give an example (see attached)
I have sales volume of SKU by date dimension ( though this data is not much relevant because i will focus on single day only).Lets say on 1Jan2019 the SKU volume is 100
User will create multiple rules (in a numbered list) . The rules are defined like this
Total Rule Volume
3. The user wants to convert the SKU1 volume to other sku based on below logic ( I strongly suggest to check attached excel)
4. On a given day ,in our case 1-Jan, apply rule 1 because it has rank 1.
5. I have to deduct 40% of my SKU1 volume of the day 0.4 * 100 =40 but before deducting I must check if my Rule#1 has sufficient volume left. this is rule opening volume (50).SInce 40<50 I can deduct the entire amount.See attached excel.
6. Now I must apply Rule#2. Required deduction is 0.3*100 = 30 but my Rule beginning volume is only 20 so I must deduct only 20 (here I have to do another check that SKU beginning volume is also higher than 20 or not else i must take the sku beginning volume. this is apparent in step 7 below)
7. Deduction at Rule#3 is 0.4*100 = 40 but as my SKU beginning volume is only 40 I can only deduct 40 ( given the fact that my Rule#3 volume is higher 200> 40). Hence my SKU ending will be 0 after all rules are applied.
The problem comes because I can not check the actual deductions till the last Rule. In case of time dimension I could have used previous but for rankcumulate it does not work(circular reference)
There is an additional challenge on top of this. If Rule#1: SKU1 -> SKU2 and Rule#2: is SKU2 -> SKU3 then in this case the SKU2 volume will be original volume plus extra volume gained from Rule#1
Thanks for the solution it works. But I am stuck with the circular reference part as the requirement is that user can create rules like below. Rule 1 output gives 50 volume for SKU2. For applying Rule my beginning volume is 150(=100+50). Similarly the output of Rule 2 is 30% * 150 = 45 this becomes additional volume for SKU3.
origin destination start date Rule% Volume RuleRank
SKU1 SKU2 1/1/2019 50% 500 1
SKU2 SKU3 1/1/2019 30% 200 2
SKU3 SKU4 1/1/2019 10 1000 3
There is further challenge :D
User can define rules like shown below (he can create as many rules as he require)
origin destination start date Rule% Volume RuleRank
SKU1 SKU2 1/1/2019 100% 500 1
SKU1 SKU3 1/1/2019 100% 500 2
SKU1 SKU4 1/1/2019 100% 500 3
Since Rule 1 will consume all origin volume rule2 and rule2 will not be applied. Rule1 will end on 5Jan because that is when Rule Volume(500) will be consumed.Rule2 will start on 6jan and end on 10 and so on.
This has become difficult as I am not able to use actual time dimension as volume of data is huge and there is very little sparsity and I only need 120days for my timescale.
[This line item checks to see if the rank of the individual row is before the rank of the Destination SKU the first time the Destination SKU appears in the list. If this test is TRUE, then the allocated units can be sent to the Destination SKU]
[This line item will send the relevant SKU into the subsequent modules, i.e. Allocated Units 1, Allocated Units 2, etc]
'Allocated Units 1' has the following line item:
'Allocated Units 2' has the following line items:
Note that 'Allocated Units 2' has an additional line item, i.e. 'Contributed Units'.
'Allocated Units 3' has the following line items:
Note that the formula of 'Contributed Unit' in this module is different from 'Contributed Units' of 'Allocated Units 2'.
The final results are captured in 'Allocated Units (Total)' shown below, i.e. Alloc Units Final. Note that 'Allocated Units (Total)' is by 'Rules #, SKU'
In summary, the model map is as shown below:
'Allocated Units 1' gets the 'Total' from 'Rule Module';
'Allocated Units 2' gets the 'Total' from 'Rule Module' and contribution from 'Allocated Units 1';
'Allocated Units 3' gets the 'Total' from 'Rule Module' and contributions from 'Allocated Units 1' and 'Allocated Units 2'
End results from 'Allocated Units 1', 'Allocated Units 2' and 'Allocated Units 3' are aggregated in 'Allocated Units (Total)'
The number of 'Allocation Units X' modules is equivalent to the number of rules, because it's possible that we have a different SKU entered in every rule, hence you can imagine how big the model map can potentially become.
Thanks for the time,patience and very elaborate feasible solution which works. But as you mentioned that the solution works fine for limited number of rules but (sigh) the user can create rules upwards of 300.
Hence I came up with a approach to resolve the circular reference issue. since the user requirement required that SKU ending volume and Rule ending volume is tracked for every instance (Fake Date/Rule/territory combination) it became quite difficult. The only lists that allows me to point to previous entry is Time & Version through Previous() and previousversion(). I mapped Dates to version and Rules to Dates. I created 125 versions (as customer data will have 120 days) and used time in day format. Thus allowing me to create upto 365 rules. The reason I did not map fake dates to Time is that I can use LAG() in time dimension to fetch the adjustment data of *any* rule above it. with this I can take output of any of the rule above it as input to get the loop working.
This approach is not without its downsides
1) The performance is really bad (even with limited data set) as every cell needs to be calculated one after another and parallel calculations can not work due to the nature of the problem
2) for some reason the size is much higher than anticipated (upto 10 times) .I think this is probably due to Version dimension taking more size??Not sure I am just guessing.
For now this is the only solution which appears to be working for me . If you think if there is any easier way please suggest.