topic Re: Restrict fields in aggregate function in QlikView

Restrict fields in aggregate function

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have a expression like:

Avg(Aggr(if(Isnull(Sum(Column1)),5,if(Isnull(Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1)),5,

if((Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1))=1,5,

if((Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1))>=0.99,4,

if((Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1))>=0.97,3,

if((Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1))>=0.95,2,

if((Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}>}Column1))>=0.93,1,0)))))))

,MappingColumn1,MappingColumn2))

I want that the value doesn't change on selection of MappingColumn2

I have tried 'MappingColumn2=' in the set analysis part but its not working.

Pls help.

Regards,

Anjali Gupta

Re: Restrict fields in aggregate function

swuehl — Sun, 29 May 2016 09:04:27 GMT

You may need to add a set expression like

{<MappingColumn2= >}

to every aggregation function, also to the outer aggregation function of the aggr() (the very first Avg()), implicite Only() functions, and if you are using dollar sign expansions evaluate expressions, also to these expresssion's aggregation function.

Hence, to show the first few places to check:

Avg({<MappingColumn2= >}

Aggr(

if(Isnull(Sum({<MappingColumn2= >}Column1)),5,

if(Isnull(Sum({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'}, MappingColumn2= >}Column1) / count({<Link_Flag={'Link1'},[Call_Year-Month]={'$(vMaxMonthMinus2)'},MappingColumn2=>}Column1)),5,

etc.

Not sure if you really need it at all places, set analysis is depending on your sets, field values and relation between fields in the model.

But to be on the safe side, add the set analysis part to every aggregation function (and don't forget the implicite Only() aggregations and variables).

edit: I would start with the outer aggregation function (Avg() ) and then check if this already does the job, then add more set expression changes as needed.

Re: Restrict fields in aggregate function

Mon, 30 May 2016 13:14:42 GMT

Thanks swuehl,

I was missing MappingColumn2= in the variable. Hence, the issue.

Regards,

Anjali Gupta