Restrict fields in aggregate function

[#1]

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

[swuehl · Accepted solution]

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.

[#3]

Thanks swuehl,

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

Regards,

Anjali Gupta