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

Try this:

Sum(Aggr(Count(Distinct Upper([Unique Report])), YourDimensions))

Replace YourDimensions with your chart dimensions here

Not applicable
Author

Where do i put this formula? Currently this formula exists in the measures and it is giving me correct numbers. The problem is in the totals. Where do I enter the formula for Totals?

sunny_talwar

Replace this with your current measure

Not applicable
Author

ok, I did that. Now the total is adding up correctly, however the measure is incorrect. This is the new formula I added in the measure.

Sum(Aggr(Count(Distinct Upper([Unique Report])), [Max Days to Submit],[Approved Date]))

The Row formula is (I didn't change it)

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

The Column Formula is (I didn't change it)

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' ) )))

Thanks for your help

sunny_talwar

You might have to move your calculated dimensions into the script.

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

          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 NewField


and then use this:

Sum(Aggr(Count(Distinct Upper([Unique Report])), MonthYear, NewField))

Not applicable
Author

You know I did exactly that and it Worked!! Great!! Thank you so much!

One small question: The zeros are coming in as dashes. How do I make them come up as zeros?

Thanks for your help!

Not applicable
Author

I was also told by someone that to bring the Totals down below in the pivot, I need to create a "Totals" in the script and add it. How would it work in this case? Meaning what would the script say?

sunny_talwar

I am not full aware of Qlik Sense's functionality, there might be a way to add 0 for nulls, but I am not aware of how to do this. May be reddys310‌ can offer some advice here

sunny_talwar

Total in the script? You want to now calculate them in the script instead of front end? I would ask you for a sample to help you better here. Just few rows of raw data with the expected output you would need.

Best,

Sunny