Set analysis with aggr sum

[Shir63]

hi everyone,

i have a formula that works:

sum(Aggr(Sum({<[Qtr Diff] = {"$(=max([Qtr Diff]))"} >} [OPP2.COUNT_FULL_PATH]), [OPP2.OPPORTUNITY_ID] , [OPP2.COUNT_FULL_PATH]))

when i want to divide my  "[OPP2.COUNT_FULL_PATH]" in another field it doesn't let me.

this is the formula i need:

sum(Aggr(Sum({<[Qtr Diff] = {"$(=max([Qtr Diff]))"} >} [OPP2.COUNT_FULL_PATH]  / count([OPP2.Count_Line_ID])), [OPP2.OPPORTUNITY_ID] , [OPP2.COUNT_FULL_PATH] / count([OPP2.Count_Line_ID])))

any thoughts what am i doing wrong?

thank you!!!

[JuanGerardo]

Hi @Shir63, Aggr() expects dimensions after your expression (first parameter), but you are adding an expression ([OPP2.COUNT_FULL_PATH] / count([OPP2.Count_Line_ID]) as dimension in your third parameter. Probably you are looking for:

sum(Aggr(Sum({<[Qtr Diff] = {"$(=max([Qtr Diff]))"} >} [OPP2.COUNT_FULL_PATH]  / count([OPP2.Count_Line_ID])), [OPP2.OPPORTUNITY_ID], [OPP2.COUNT_FULL_PATH])))

JG

[Shir63]

hi,

thank you 

 but it results me with 0 in the KPI.

sum(Aggr(Sum({<[Qtr Diff] = {"$(=max([Qtr Diff]))"} >} [OPP2.COUNT_FULL_PATH]  / count([OPP2.Count_Line_ID])), [OPP2.OPPORTUNITY_ID], [OPP2.COUNT_FULL_PATH]))

[Shir63 · Accepted solution]

but, thank to your explanation i managed to find a solution that works

if(QuarterStart(today())=max(QuarterDateStart),

sum(aggr(sum({<[Qtr Diff] = {"$(=max([Qtr Diff]))"}>}[OPP2.COUNT_FULL_PATH])/count([OPP2.Count_Line_ID]),[OPP2.OPPORTUNITY_ID])),
sum(aggr(sum([OPP2.COUNT_FULL_PATH])/count([OPP2.Count_Line_ID]),[OPP2.OPPORTUNITY_ID])))