Need you help in calculating the credit for sales rep month by month for Orders he is working on.
|Order#||Order Status||Initial Order Date||Order Change Date||Net Value||Delta||Payout|
|111||Initial Order||01-Jan-18||100||100||100||Jan Credit|
|222||Initial Order||01-Feb-18||20||20||20||Feb Credit|
I have created some sample data above for your reference
order 111# :
->Initial order came in the month of January and net value is 100 .100 value is considered for sales rep's commision calculation.
->There was a change in the order(order status is taken as reference) in the month of Feb. new net value is 150. So in this case Delta between the current net value and previous net value is considered for commision calculation.
->Then another changed record arrived in the month of April, So delta between April and the most previous period(Feb) is used for commision calculation.
Could you please help me to derivie this delta value between the current period and the most recent period of same orders.
The current period delta calculation should not impact any of the previos period's crediting and payout.
Solved! Go to Solution.
There is one way to do it that is very robust even if the list you are loading is not sorted. Here is what you will need to do:
Below I will walk you through the logic. Please also check the screenshots attached.
I hope this is helpful. The only risk you are running here it that you have more changes per order than items in your ranking list, so make sure you include a check for this.
Thank you so much for you help with detail explanation.
I have implemented this and it works wonderful.
One last Question, Does it will impact the overall performance of model as we have used the Rank function and we will be having lot many records.
There should be no issues with performance, but you could run into a problem with size: if you have 100 000 Orders and each can be changed up to 100 times, M 02 module will instantly grow to 10 million cells. Hope this will not be an issue for your model.
Unless I've missed something there is a really simple solution
1. Assuming you have a module that stores your Orders, I've added a line item for a date lookup with the following formula:
IF ISNOTBLANK(Order Change Date) THEN Order Change Date ELSE Initial Order Date
Order is a formatted list
2. Create a "Commission Payout" module dimensioned by month and add three line items
a. Changed Value: Order Details.Net Value[SUM: Order Details.Order, SUM: Order Details.Date Lookup]
b. Cumulate Changed: IF Changed Value = 0 THEN Changed Value + PREVIOUS(Cumulate Changed) ELSE Changed Value
c. Payout: Cumulate Changed - PREVIOUS(Cumulate Changed)
I forgot about DECUMULATE.
You can set the Payout formula to DECUMULATE(Cumulate Changed) to be even simpler
I'll just add some detail around the RANK function to highlight that it would be better to build a solution without it where possible. The RANK function performs a lot slower because the calculation has to be done in a single calculation, it can't calculate multiple parts of dimension and combine them, it wouldn't have the same order. So on larger dimensions this performance hit becomes noticeable.
Here's the info we provide in anapedia:
The RANK function could be slow to evaluate at large data volumes, due to performance characteristics of sorting an arbitrary set that takes O(n * ln(n)). An artificial limit of 10M cells is imposed to prevent ranking of large datasets that would slow down the server: if this is exceeded, the model is rolled back. Note that when used against datasets of more than 1 million cells, any change in source values could result in calculations that take a few seconds.
@Ankitjain . Could you let us know if the new proposed solution works for you
Thank you for the suggesting such a great sulution.
I have tried to implement it and it is working great.The only possible issue i can see is as we are crossing a order with month which will increase sparcity and this will fet worse if we maintain more then 1 year data.
I'm glad it's working for you.
In anwering your question, there is almost always a trade off between performance, sparsity, complexity etc. In terms of the calculation here, yes, it will increase sparsity, but not necessarily calculation time. Sparse structures do take up more cell count, but that doesn't mean the calculation time will be worse. In fact performance often improves with a sparse structure.
In this case, one simple step to mitigate the cell count is to turn off all of the summary options in the Payout module. This is a calculation module that doesn't need subtotals by time or the order hierarchy. That will help speed up the calculation and keep the size down.
You could also look at using Time Ranges to ensure that the calculaiton time frame is applicable only for the future years, not any past years
I hope that helps