Sum of values not matching in pivot table

[Anonymous]

Hi,

I have a pivot table where I'm calculating total count of files in one column, total count of files where file_type = email in the next column, total count of files where file_type = document in the next column and the total count of files by month as shown below....

Total all files (documents+emails) = 85, total emails = 31 and total documents = 55 which sums up to 86 and not matching with the total all files value. But the total emails = 31 + total documents = 55 should match with Total all files.

Also Jun-16 to Dec-16, total count (documents+emails) came down to 87 and not matching with the total all files value. In the monthly split, there is a chance that same file is used in different months and that might be causing the difference.

Expressions used:

Total all files:

num(Aggr(count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"}>}distinct ID),USER),'###########,###'))

Total emails:

num(Aggr(count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"},FILE_TYPE = 'Email'>}distinct ID),USER),'###########,###'))

Total documents:

num(Aggr(count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"},FILE_TYPE = Document>}distinct ID),USER),'###########,###'))

Can someone please help me fix this ASAP. Thanks!

[rubenmarin]

Hi Roshitha, most probably one email and document have the same ID, so counting distinct will count this two only once.

If this is what's happening the options are:

- Create a field in script adding a code for each FILE_TYPE, so each ID has a diiferent ID, ie:

LOAD FILE_TYPE &'_'& ID as UniqueID

- Sum both expressions:

num(Aggr(count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"},FILE_TYPE = 'Email'>}distinct ID)

+

count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"},FILE_TYPE = Document>}distinct ID)

,USER),'###########,###'))

- Use a concatenated key in expression:

num(Aggr(count({<DATE ={">=$(=Date(AddMonths(MonthStart(Max(DATE)),-11),'MM-DD-YYYY')) <= $(=Date(AddMonths(MonthEnd(Max(DATE)),-11),'MM-DD-YYYY'))"}>}distinct FILE_TYPE &'_'& ID),USER),'###########,###'))

Edit: probably the same is happening with months

LOAD DateField &'_'& FILE_TYPE &'_'& ID as UniqueID

Autonumber can be used to avoid loading long strings:

LOAD Autonumber(DateField &'_'& FILE_TYPE &'_'& ID) as UniqueID

[Anonymous]

‌Thank you so much for your prompt response Marin.

I’ll try this.