Ignore selection on set analysis

[#1]

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!

[swuehl]

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

[#3]

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!

[swuehl]

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)

)

[#5 · Accepted solution]

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

    )

)