topic Ignore selection on set analysis in QlikView

Ignore selection on set analysis

Sun, 04 Dec 2011 18:11:04 GMT

I am preparing a combo chart (stacked bars and a line) that is targeted towards a single selected client.. client field name is - CMU2_NM

The chart has only a single dimension as - CalendarMonthName. For selected client (CMU2_NM), the chart shows other values. And additionally I would like to add an average line. So am first trying to calculate sum() for all CMU2_NM and then will divide by # of CMU2_NM...

Added below expression to the chart, but it does not "ignore" selected CMU2_NM and returns value based on client selection...How do I fix this expression so that it will return sum for all relative consumptions for all the clients...

sum(

aggr(

(count({<ConfFlag={1}>} distinct EVENT_ID)/

count({<ConfFlag={1},CMU2_NM=>} total <CalendarMonthName> distinct EVENT_ID)

)*ACI_1_1_1_WEIGHT

,CMU2_NM)

)

Please refer to attached image where I am trying to provide more details.. Once this issue is resolved for ACI 1.1.1, then I can apply similar logic to other 25 such ACIs!

Appreciate any help.. thanks!

Ignore selection on set analysis

swuehl — Sun, 04 Dec 2011 18:48:54 GMT

Hi,

you already cleared the client selection in your denominator using

{<ConfFlag={1},CMU2_NM=>}

You probably just need to use this same set expression also in the Numerator, in total maybe like:

sum(

aggr(

(count({<ConfFlag={1}, CMU2_NM= >} distinct EVENT_ID)/

count({<ConfFlag={1},CMU2_NM=>} total <CalendarMonthName> distinct EVENT_ID)

)*ACI_1_1_1_WEIGHT

,CMU2_NM)

)

Hope this helps,

Stefan

Ignore selection on set analysis

Sun, 04 Dec 2011 18:55:05 GMT

Hi Stefan

Thanks for replying..

Yes, I tried that apporach.. but that didn't work. It did not ignroe the CMU2_NM..

Basically the temp table created using AGGR function should be for all CMU2_NM irrespective of whether CMU2_NM was selected or not...

I also tried with below... but that also does not ignore CMU2_NM selection..

sum({<CMU2_NM=>}

aggr(

(count({<ConfFlag={1}>} distinct EVENT_ID)/

count({<ConfFlag={1},CMU2_NM=>} total <CalendarMonthName> distinct EVENT_ID)

)*ACI_1_1_1_WEIGHT

,CMU2_NM)

)

When I dont select any CMU2_NM, then above expression correctly returns .1242 which is the sum for all CMU2_NM.. I want to achieve the same result, only by ignoring CMU2_NM if it is selected!

Ignore selection on set analysis

swuehl — Sun, 04 Dec 2011 20:08:00 GMT

By default, the aggregation function will aggregate over the set of possible records defined by the selection. So we might state the set also for the aggr() function:

sum(

aggr( {<CMU2_NM= >}

(count({<ConfFlag={1}>} distinct EVENT_ID)/

count({<ConfFlag={1},CMU2_NM=>} total <CalendarMonthName> distinct EVENT_ID)

)*ACI_1_1_1_WEIGHT

,CMU2_NM)

)

Ignore selection on set analysis

Sun, 04 Dec 2011 20:23:48 GMT

Stephan, thanks for providing the direction... I was able to resolve the issue with below expression -

sum({<CMU2_NM=>}

aggr

(

count({<ConfFlag={1},CMU2_NM=>} distinct EVENT_ID)

count({<ConfFlag={1},CMU2_NM=>} total <CalendarMonthName> distinct EVENT_ID)

*SUM(total distinct ACI_1_1_1_WEIGHT)

,CMU2_NM,CalendarMonthName

)