Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Thank you so much for your prompt response Marin.
I’ll try this.