Rankcumulate till previous Rank
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
|Rule||OriginSKU||Destination SKU||Date||Deduction Percent||Total Rule Volume||Rule Rank|
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 in advance.