Scripting Help with Total Function

Wed, 05 Jun 2013 15:17:08 GMT

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!

Re: Scripting Help with Total Function

Anonymous — Wed, 05 Jun 2013 15:48:10 GMT

Hello,

i hope you serve, used SET Analysis

RollingSales/sum(Total({1}TotalSales2))

1 ignores the selections

Wed, 05 Jun 2013 15:49:22 GMT

if you prefer in the script, see attached

Wed, 05 Jun 2013 17:03:40 GMT

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.