3 Replies Latest reply: Jun 5, 2013 1:03 PM by Melanie Feller RSS

    Scripting Help with Total Function

      I have two fields, Item and TotalSales2 which are uploaded from a SQL query. 

      There is only one instance of each Item and the TotalSales2 represents all the sales in the last year for that item.

      I use these two values to determine an ABC classification based on cumulative percent sales.

       

      I was able to do this as an expression, but the values change as people filter by other fields.  I want the field to be constant.

      I think the easiest way to do this is in the script and load the final values.

      However, my expression calculation is not working in the script.

       

      Can someone please look at what I have an help me write a correct calculation in the script?

       

      Here is what I have:

       

      In Script[RollingSales Calculation]

      If(rowno()=1,TotalSales2, Peek(RollingSales)+TotalSales2) AS RollingSales

       

      In Expression [Cumulative%Sales]:  (A portion of the below calculation, seperated to make the function easier to read)

      RollingSales/sum(Total(TotalSales2))

       

      In Expression [ABC Classification]

      if((RollingSales/sum(Total(TotalSales2)))<=.8,'A',if((RollingSales/sum(Total(TotalSales2)))<=.95,'B','C'))

       

      So the ABC classficiation is basically looking at Cumulative % Sales and comparing it to 80% or 95% and assigning values A, B, C

       

      Thanks!