Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hello,
i hope you serve, used SET Analysis
RollingSales/sum(Total({1}TotalSales2))
1 ignores the selections
Hello,
i hope you serve, used SET Analysis
RollingSales/sum(Total({1}TotalSales2))
1 ignores the selections
if you prefer in the script, see attached
Set analysis was much simplier in this case than I anticipated, so I went with that option.
Note, it actually had to be this: RollingSales/sum({1}Total(TotalSales2)), the location of the {1} was off.