We are developing a report to see a car part number selling status for eg: Fast Moving, Medium Moving or Slow Moving based on business defined logic. The logic is as below.
1) Count how many sale order lines the parts are
2) Find the percentage of sale order line of each part against total number of sale order lines
3) Sort the result descending based on percentage, and mark first 80 percentage parts as Fast moving, next 15 % as medium and next 5% as Slow moving.
I can bring below result using analytical view based on sale order line item table.
But i need "Speed" Column to be calculated based on the above logic.
I am thinking of using Cursor in Scripted Calculation view to loop through the each and every record in the table and sum the percentage in each and every row in a sum variable and mark as "Fast" till it reaches 80 if it reaches 80 then clear the sum variable, then again mark as Medium till it reaches 15 then clear the variable, then mark rest as "Slow".
Please let me know if this is right and best approach. As per my knowledge it is not possible to achieve this in Graphical view, please correct me if i am wrong. Is there any other way i can use in scripted calc view to achieve this.