Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hopkinsc
Valued Contributor II

Set Analysis help

Hi, i need to write an expression using Set Analysis to give me the result of the following..

Wastage on CHILLED dept as a % of CHILLED sales.

my fields are :

[Stock Movement Reason Code] - (Waste codes are 3 and 5)

[Dept] - (N is CHILLED)

[StockValue) - (Waste Value)

[SalesValue] - (Sales Value)

I have tried the following but i can't get the correct result...

sum  ( {<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<Dept={'N'}>}SalesValue)

Can anyone tell me what is wrong with my expression please?

3 Replies
Not applicable

Re: Set Analysis help

Hi Chris,

there's nothing syntactically wrong with your expression.. though you would have to apply the same set analysis expression to both StockValue and SalesValue fields.

Try the following expression

sum({<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<[Stock Movement Reason Code] ={3, 5},Dept={'N'}>}SalesValue)

See if this helps.

Rgrds,

Abhinava

hopkinsc
Valued Contributor II

Set Analysis help

Hi, i have got it working, i can't use the same set analysis for the 2nd part as i need to divide it by ALL CHILLED sales regardless of any wastage or not.

Anyway i have it working now (my posted expression worked, the problem was i had a field selected which shouldnt have been)

I am affraid that the end user would get incorrect figures if they make the same mistake as i did.So how would i use the same expression but exclude the selections made on [Stock Movement Reason Code] on the 2nd part of the expression?

SunilChauhan
Esteemed Contributor

Set Analysis help

use below code

sum  ( {1<[Stock Movement Reason Code] ={3, 5}, Dept={'N'}>} StockValue)/Sum({$<Dept={'N'}>}SalesValue)