Rankcumulate till previous Rank

GauravT_*15211

Hi All,

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)

 

1. 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
2. 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
#1	SKU1	SKU2	1-Jan-19	40%	50	1
#2	SKU1	SKU3	1-Jan-19	30%	20	2
#3	SKU1	SKU4	1-Jan-19	40%	200	3

 

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.

LipChean_Soh

Hi gthakur,

 

Without looking at the additional challenge first, would the following solve the first part of your problem?

Please note that the Rule Module has different numbers compared to your example.

The final answer should be 'Allocated Units 2.Alloc Units Final'

 

Formula for 

1. Alloc Units v1 = IF Cumulate Needed Units < Total Needed Units THEN IF Available Units > Needed Units THEN Needed Units ELSE Available Units ELSE 0

2. Alloc Units v2 = IF Cumulate Needed Units > Total Needed Units THEN Total Needed Units - 'Cumulate Alloc Units v1' ELSE 0

 

Basically, Alloc Units v2 deals with the last rule.

 

Thanks,

LipChean

GauravT_*15211

Hi LipChean,

 

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 😄

 

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.

 

LipChean_Soh

Hi gthakur,

 

What if SKU3 has a destination of SKU2 (in italics below), but the Rule Rank is 3?

What's the expected result?

 

origin    destination   start date   Rule%    Volume   RuleRank

SKU1     SKU2          1/1/2019     50%        500            1

SKU2     SKU3          1/1/2019     30%        200            2

SKU3     SKU2           1/1/2019     10          1000          3

 

Thanks,

LipChean

GauravT_*15211

Hi LipChean,

 

In this case Rule3 output will not become input of SKU2 of rule 2 as rules must be applied in order.And according to requirements this is a feasible scenario and order becomes really important.

 

Thanks & Regards,

Gaurav Thakur

LipChean_Soh

Hi @GauravT ,

 

Does that mean Rule 3 is no longer valid, and there won't be any calculation applied for Rule 3?

 

Thanks,

LipChean

GauravT_*15211

Hi @LipChean_Soh 

 

Thanks for the reply.

In this case Rule 3 will remain valid as it can still apply on original sku units from sales plan volume. But it wont have any additional units from previous rules.

 

Gaurav Thakur

LipChean_Soh

Hi @GauravT ,

 

This is a long post, and i wouldn't recommend this approach if you have a large number of rules, for reasons that will be obvious later.

 

3 modules are shown above, i.e. Total Units, Rule Module, SKU Admin.

'Total Units' is where you enter the Total Units to be allocated by the different rules.

 

'Rule Module' is where you enter the individual rules. It has input line items shown above + the following calculationline items:

1. Send to Target SKU? = Rule Rank < SKU Admin.First Occurrence[LOOKUP: Destination SKU]

    [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]

2. Applicable Rank by SKU = RANK(Rule Rank, ASCENDING, SEQUENTIAL, ISNOTBLANK(Origin SKU), Origin SKU)

3. Final Rank = RANK(Applicable Rank by SKU, ASCENDING, SEQUENTIAL, Applicable Rank by SKU = 1)

   [This line item ranks the SKU by the order of which they appear the first time in the 'Rule Module', and this determines which 'Allocation Module' the relevant SKU is treated.]

4. Rank SKU = FINDITEM(#Rank, TEXT(Final Rank))

Note: These 4 line items decide which SKU goes to which Allocation Module. These are the 4 key line items of the model.

 

'SKU Admin' is

1. First Occurrence = Rule Module.Rule Rank[MIN: Rule Module.Origin SKU]

 

4 modules are shown above, i.e. Rule Module, SKU Rank, Allocated Units 1, Allocated Units 2.

'Rule Module' has been described previously.

 

'SKU Rank' has the following line item:

1. SKU2 = Rule Module.Origin SKU[FIRSTNONBLANK: Rule Module.Rank 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,

LipChean

GauravT_*15211

Hi @LipChean_Soh ,

 

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.

 

Thanks,

Gaurav Thakur