Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hello,

i hope you serve, used SET Analysis

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

1 ignores the selections

View solution in original post

3 Replies
Anonymous
Not applicable
Author

Hello,

i hope you serve, used SET Analysis

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

1 ignores the selections

Not applicable
Author

if you prefer in the script, see attached

Not applicable
Author

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.