Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Totals don't add up in Pivot Table

Please take a look at this pivot table. The individual numbers are correct. But the Totals do not add up. The formula I have for calculating individual numbers is Count(Distinct upper([Unique Report])). How do I get the totals to agree? I have also attached the excel version.

Capture.PNG

stalwar1‌ can you help?

32 Replies
sunny_talwar

Script:

Table:

LOAD Date(MonthStart([Approved Date]), 'MMM-YY') as MonthYear,

    If(Days < 16 or IsNull(Days),' Within Policy',

    If(Days < 31, '16-30 Days',

    If(Days <61, '31-60 Days',

    If(Days < 91, '61-90 Days', '90+ Days' )))) as Bucket,

    [Unique Report];

LOAD * INLINE [

Approved Date, Days, Unique Report

10/6/2015, 2, abcdef

10/28/2015, 18, abfrgd

4/28/2016, 25, okyhfs

10/28/2015, 20, abcdef

10/28/2015, 20, abcdeh

];

Dim:

LOAD * INLINE [

Dim

1

2

];

Dim2:

LOAD * INLINE [

Dim2

1

2

];

Dimensions

1) =Pick(Dim, MonthName(MonthYear), 'TOTAL')

2) =Pick(Dim2, Bucket, 'TOTAL')

Expression

If(Dim = 1 and Dim2 = 1, Count(Distinct Upper([Unique Report])), Count(Upper([Unique Report])))

Capture.PNG

Also, you will need a sort expression

Dimension1 -> Dim

Dimension2 -> Dim2

sunny_talwar

Script looks good to me. How does your dimension and expression look like?

Not applicable
Author

This is row dimension

Pick(Dim, MonthName(Months), 'Totals')

This is column dimension

Pick(Dim2, Aging, 'TOTAL')

This is measures

if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months)))

sunny_talwar

How about this as the expression:

If(Dim = 1 and Dim2 = 1,


if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),


Count(Upper([Unique Report])))

Not applicable
Author

When I use that expression, I get a total that is about 10 times what it should be.

sunny_talwar

How about this:

If(Dim = 1 and Dim2 = 1,


if(Isnull(Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),0,Sum(Aggr(Count(Distinct Upper([Unique Report])), Aging,Months))),


Count(DISTINCT Upper([Unique Report])))

Right now its all guess work as I don't have your actual scenario to look at.

Not applicable
Author

This is progress as I am getting a number that is closer. It is the sum total where it is not making a distinction between the change in the "months" or "Aging"

sunny_talwar

It is a sum total.... not sure I understand... can you explain with numbers

Not applicable
Author

Look at my original post with numbers. I am getting exactly those numbers now. We were able to solve that issue with your solution. Everything was ok but I needed the totals at the bottom. So we took a slightly different approach. And the good thing is that I am able to get the total to the bottom. But the numbers need to be fixed.

Not applicable
Author

We are almost there stalwar1

Don't give up on me now.

Thanks for all your help so far.