Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Restrict fields in aggregate function

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Restrict fields in aggregate function

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.

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: Restrict fields in aggregate function

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.

View solution in original post

Highlighted
Not applicable

Re: Restrict fields in aggregate function

Thanks swuehl,

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

Regards,

Anjali Gupta