Question on Aggr and set analysis

[gerrycastellino]

I have a chart where I need to aggregate by Year, Month, Country  all managers with <=  2 direct reports.

When I select an individual month I get the right results in my count (264).

When I select a range of months, I get an incorrect result:

My Expression is:

Count(

Aggr(

Sum({<SL_SPRVSRY_ORG_WID={"=Aggr(Sum(TOTAL_ROLL_UP_COUNT),SL_SPRVSRY_ORG_WID)<=2"}>} TOTAL_ROLL_UP_COUNT)

,SL_SPRVSRY_ORG_WID

)

)

My dimensions are Year, Month, country.

SL_SPRVSRY_ORG_WID  is a manager ID.

Thankx.

Gerry.

[vishsaggi]

Can you share a sample to work on? may be try like

= Count(

         Aggr(

                    Sum({<SL_SPRVSRY_ORG_WID={"=Aggr(Sum(TOTAL_ROLL_UP_COUNT),SL_SPRVSRY_ORG_WID)<=2"}>} TOTAL_ROLL_UP_COUNT)

,SL_SPRVSRY_ORG_WID, [Fiscal Month]

                )

              )

[sunny_talwar · Accepted solution]

May be this

Count(Aggr(If(Sum(TOTAL_ROLL_UP_COUNT) <= 2, 1, 0), SL_SPRVSRY_ORG_WID, Year, Month, Country))

[gerrycastellino]

Sunny:

Your solution worked well, with a little tweak:

I've put the DR in a variable, which makes it more flexbile in the expr.

I'm having a small problem in my pivot table, the TOTAL numbers are accurate, but not displaying in line with the countries, as below:

[sunny_talwar]

Not entirely sure, but may be you need to add Country to the Aggr() function....