Inserting set analysis in an aggr function with a variable

[danaleota1]

Hello I have this aggr function for a run chart.  I want to include set analysis to ignore the [Month-Year] filter and respect the [Agent] filter.  So that as you filter by the [Month-Year] filter the run chart will remain fixed unless you change the [Agent] filter.  

sum(AGGR(((SUM(ACDCALLS)/SUM(TOTAL <[Month-Year],[Agent]> ACDCALLS))* $(f_GroupScore)), REPORT_GROUP, [Month-Year], Agent))

I started with this:

sum(AGGR((SUM({<[Month-Year]= 1::[Month-Year], [Agent] = $::[Agent]>} ACDCALLS)/SUM({<[Month-Year]= 1::[Month-Year], [Agent] = $::[Agent]>} Total <[Month-Year],[Agent]> ACDCALLS))*$(f_GroupScore)),REPORT_GROUP,[Month-Year],Agent))

But that only returns 0's.  Any leads would be great thank you.  

[sunny_talwar]

Try this

Sum({<[Month-Year]>} Aggr(((Sum({<[Month-Year]>} ACDCALLS)/Sum({<[Month-Year]>} TOTAL <[Month-Year], [Agent]> ACDCALLS))* $(f_GroupScore)), REPORT_GROUP, [Month-Year], Agent))

[danaleota1]

no it removed the 0's but when you filer by Month-Year all the other months show up as 0.  Also the variable,  f_GroupScore =  

If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore1,
1,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore2,
2,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore3,
3,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore4,
4,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore5,
5,
If( (Sum(ACDTIME) + Sum(ACWTIME) + Sum(HOLDTIME)) / Sum(ACDCALLS) >= GroupScore0,
1,
Null()
)
)
)
)
)
)

does the set analysis need to be inserted into all of these agg functions as well?  Thanks again.  

[sunny_talwar · Accepted solution]

Yes, anywhere you see Sum(), you will need to ignore selection in Month-Year. Also, GroupScore1,2,3 are fields too? You might need to ignore selection in those as well.

[danaleota1 · Accepted solution]

Yes they are fields as well.  Okay it worked @sunny_talwar   Here it is.  I removed the variable name and inserted the original expression from load editor and added all the {<[Month-Year]>}  it is now ignoring this filter.  Thanks!   

sum({<[Month-Year] >} AGGR(((SUM({<[Month-Year] >} ACDCALLS)/SUM({<[Month-Year]>} TOTAL <[Month-Year],[Agent]> ACDCALLS))*
If( (Sum({<[Month-Year]>} ACDTIME) + Sum({<[Month-Year]>}ACWTIME) + Sum({<[Month-Year]>} HOLDTIME)) / Sum({<[Month-Year]>} ACDCALLS) >= avg({<[Month-Year]>} GroupScore1),
1,
If( (Sum({<[Month-Year]>} ACDTIME) + Sum({<[Month-Year]>} ACWTIME) + Sum({<[Month-Year]>} HOLDTIME)) / Sum({<[Month-Year]>} ACDCALLS) >= avg({<[Month-Year]>} GroupScore2),
2,
If( (Sum({<[Month-Year]>}ACDTIME) + Sum({<[Month-Year]>}ACWTIME) + Sum({<[Month-Year]>}HOLDTIME)) / Sum({<[Month-Year]>}ACDCALLS) >= avg({<[Month-Year]>}GroupScore3),
3,
If( (Sum({<[Month-Year]>} ACDTIME) + Sum({<[Month-Year]>} ACWTIME) + Sum({<[Month-Year]>} HOLDTIME)) / Sum({<[Month-Year]>} ACDCALLS) >= avg({<[Month-Year]>} GroupScore4),
4,
If( (Sum({<[Month-Year]>} ACDTIME) + Sum({<[Month-Year]>} ACWTIME) + Sum({<[Month-Year]>} HOLDTIME)) / Sum({<[Month-Year]>} ACDCALLS) >= avg({<[Month-Year]>} GroupScore5),
5,
If( (Sum({<[Month-Year]>}ACDTIME) + Sum({<[Month-Year]>}ACWTIME) + Sum({<[Month-Year]>}HOLDTIME)) / Sum({<[Month-Year]>}ACDCALLS) >= avg({<[Month-Year]>}GroupScore0),
1,
Null()
)
)
)
)
)
)),
REPORT_GROUP, [Month-Year], Agent))