Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
chinnukool
Contributor III
Contributor III

Sum of values not matching in pivot table

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!

2 Replies
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


chinnukool
Contributor III
Contributor III
Author

‌Thank you so much for your prompt response Marin.

I’ll try this.