Set Analysis Issue

[rahulsingh12]

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,

[Anil_Babu_Samineni]

May be share full expression and result set needed?

[jonathandienst]

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

[sunny_talwar]

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)

[rahulsingh12]

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

[sunny_talwar]

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)