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
Not applicable
Author

You see my first pic in the comments above where the totals are at the top of the pivot? I need them at the bottom instead of the top. I was told that in order to bring the total down, i need to create a "Total" in the script and add it. Not sure how to do it.

sunny_talwar

You can use ValueList() or Island table load to do this. There are many examples of how this is done. Since I don't have a sample to help you out with, you will have to fall back to that option .

Not applicable
Author

Let me send you the sample. Don't give up on me. 🙂

Not applicable
Author

Sunny, please see attached. I need the totals in the pivot to come down and to the right. Thanks for your help!

sunny_talwar

This?

Capture.PNG

Check the circled number. Do you want this as 2?

Not applicable
Author

Yes that should be 2. So the bottom total should be 3 and the grand total should be 4.

How did you get the "Total" to go to the bottom and to the right? Did it have something to do with pick?

sunny_talwar

Yes, it does

I am not sure how you can get 2 there. There are 3 unique report which fall under 16-30 bucket and are in Oct. Why won't you see 3 there?

Not applicable
Author

You are right! Sorry about that. You should be able to see 5 as the grand total.

Now I tried your formula. I got a zero for total and it is still on the top. It is not coming down.??

Thanks for the help!

Not applicable
Author

Where do you define the 'Total'? How does it know to calculate the summation?

Not applicable
Author

This is what my script looks like. It is slightly different than yours. Is that why? What should I change in my script?

[DATA]:

LOAD     [Approved Date],

               [Max Days to Submit],

               [Unique Report],

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

    If (([Max Days to Submit]) < 16 or IsNull([Max Days to Submit]),' Within Policy',If (([Max Days to Submit]) < 31, '16-30 Days',If (([Max Days to Submit]) <61, '31-60 Days',If (([Max Days to Submit]) < 91, '61-90 Days','90+ Days' ) ))) as Aging

FROM [lib://Qlik/FF Data Slide 1.xlsx]

(ooxml, embedded labels, table is DATA);

Dim:

LOAD * INLINE [

Dim

1

2

];

Dim2:

LOAD * INLINE [

Dim2

1

2

];