topic Re: Set Analysis Issue in QlikView

Set Analysis Issue

rahulsingh12 — Mon, 04 Dec 2017 07:49:08 GMT

hi I have a case as below:

State and corresponding volume and a flag for each state

STATE	VOL	FLAG
MH	10	SW
MH	20	SR
RJ	10	SR
JH	10	SR
GJ	45	SR
MH	56	SR

Here I want to calculate the if the state is MH then take vol for flag 'SW' and for all other take SR.

This vol has to be divided by the total vol to get the percentage.

The value in State field will keep changing so cannot hard code the value in expression so I thought of set analysis but it is giving me some wrong data:

sum({<STATE={'MH'},FLAG={'SW'}>}VOL)/(TOTAL VOL SW FOR MH AND SR FOR REST OF THE STATE)

SUM({<STATE-={'MH'},FLAG={'SR'}>}VOL)/(TOTAL VOL SW FOR MH AND SR FOR REST OF THE STATE)

Regards,

Re: Set Analysis Issue

Anil_Babu_Samineni — Mon, 04 Dec 2017 08:11:15 GMT

May be share full expression and result set needed?

Re: Set Analysis Issue

jonathandienst — Mon, 04 Dec 2017 08:27:45 GMT

To simplify the set expression and get % of total:

=Sum({<STATE = {'MH'}, FLAG = {'SW'}> + <STATE -= {'MH'},FLAG = {'SR'}>} VOL) /

(Sum({<STATE={'MH'},FLAG={'SW'}>+<STATE-={'MH'},FLAG={'SR'}>} TOTAL VOL)

You want to calculate total for others:

=Sum({<STATE = {'MH'}, FLAG = {'SW'}> + <STATE -= {'MH'},FLAG = {'SR'}>} VOL)/

(Sum({<STATE={'MH'},FLAG={'SW'}>+<STATE-={'MH'},FLAG={'SR'}>} TOTAL VOL)

- Sum({<STATE = {'MH'}, FLAG = {'SW'}> + <STATE -= {'MH'},FLAG = {'SR'}>} VOL))

This can be arithmetically simplified to\

=Sum({<STATE = {'MH'}, FLAG = {'SW'}> + <STATE -= {'MH'},FLAG = {'SR'}>} VOL)/

Sum({<STATE={'MH'},FLAG={'SW'}>+<STATE-={'MH'},FLAG={'SR'}>} TOTAL VOL) - 1

Re: Set Analysis Issue

sunny_talwar — Mon, 04 Dec 2017 13:50:44 GMT

Is this what you are looking to get?

I used this expression

Sum({<STATE *= {'MH'}, FLAG *= {'SW'}>+<STATE -= {'MH'}, FLAG = {'SR'}>} VOL)/Sum(TOTAL {<STATE *= {'MH'}, FLAG *= {'SW'}>+<STATE -= {'MH'}, FLAG = {'SR'}>} VOL)

Re: Set Analysis Issue

rahulsingh12 — Tue, 05 Dec 2017 05:27:15 GMT

Hi Sunny with this expression If i select GJ it shows data for GJ as well for MH.

I don't want data for MH when I select GJ.

Is there something I am doing wrong.

Regards,

Rahul

Re: Set Analysis Issue

sunny_talwar — Tue, 05 Dec 2017 09:55:48 GMT

I don't see this behavior

Only thing you might do different is to use this expression so that the percentage doesn't change to 100% when you select a STATE

Sum({<STATE *= {'MH'}, FLAG *= {'SW'}>+<STATE -= {'MH'}, FLAG = {'SR'}>} VOL)/Sum(TOTAL {<STATE = {'MH'}, FLAG *= {'SW'}>+<STATE = -{'MH'}, FLAG = {'SR'}>} VOL)