Sum Logic

[tmumaw]

Hello All,

Need a little help on a calculation. On one pivot table I am showing Avg No. of employees by month and year.  On the other pivot table I am showing the same data only adding Reason.  I need to show the Avg  No of employees on both tables.  I am using the same calculation on both, but the one with reason added is showing something different. Here is my calculation I use on both pivot tables.

sum({<YTDFlag = {1}>}EmpCt_ZEMPCT) / Count({<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE))

First pivot table is by Office, Year and Month and the other is by Office, Year, Month and Reason.

[sunny_talwar]

I guess you need to start with reading this

Average – Which average?‌

[Digvijay_Singh]

If you want same result on both tables then use this Total keyword without Reason included-

sum(Total<Office,Year,Month> {<YTDFlag = {1}>}EmpCt_ZEMPCT) / Count(Total<Office,Year,Month> {<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE))

[tmumaw]

Here is what I am seeing when I use you expression.

[Digvijay_Singh]

Not sure what is making this difference, although Aggr is slow, can u see result of this -

Aggr(sum( {<YTDFlag = {1}>}EmpCt_ZEMPCT) /Count( {<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE)),Office,Year,Month)

If you can share sample data, would help to identify the real problem..

[tmumaw]

Aggr results look better, but look at last line on the second pivot table (No Results?)

[Digvijay_Singh]

Try this -

Aggr(NODISTINCT sum( {<YTDFlag = {1}>}EmpCt_ZEMPCT) /Count( {<EmpDays = {1}, YTDFlag= {1}>}DISTINCT(Date_ZWKDATE)),Office,Year,Month)

[Digvijay_Singh]

Hi swuehl‌    

Any reason Total is showing different results here?

[sunny_talwar]

NODISTINCT should solve the problem

[tmumaw]

Thank you very much for all your help.  It appears that "NODISTINCT" fixed the problem