Announcements
cancel
Showing results for
Did you mean:
Creator III

## Question on Aggr and set analysis

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.

1 Solution

Accepted Solutions
MVP

May be this

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

4 Replies
Champion III

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]

)

)

MVP

May be this

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

Creator III
Author

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:

MVP

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

Community Browser