topic Re: Set Analysis help in QlikView

Set Analysis help

hopkinsc — Wed, 21 Sep 2011 11:30:08 GMT

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?

Re: Set Analysis help

Wed, 21 Sep 2011 11:45:26 GMT

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

Set Analysis help

hopkinsc — Wed, 21 Sep 2011 12:14:48 GMT

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?

Set Analysis help

SunilChauhan — Wed, 21 Sep 2011 13:05:41 GMT

use below code

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