Totals don't add up in Pivot Table

[#1]

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.

stalwar1‌ can you help?

[sunny_talwar]

Try this:

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

Replace YourDimensions with your chart dimensions here

[#3]

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

[#5]

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

[#7]

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!

[#8]

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