topic Re: Totals don't add up in Pivot Table in Visualization and Usability

Totals don't add up in Pivot Table

Fri, 08 Jul 2016 17:22:54 GMT

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?

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 18:11:29 GMT

Try this:

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

Replace YourDimensions with your chart dimensions here

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 20:02:35 GMT

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?

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 20:04:11 GMT

Replace this with your current measure

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 20:14:34 GMT

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

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 20:47:28 GMT

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

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

and then use this:

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

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 20:55:11 GMT

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!

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 20:59:44 GMT

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?

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 21:16:01 GMT

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

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 21:16:55 GMT

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

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 21:21:30 GMT

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.

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 21:30:24 GMT

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 .

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 21:47:10 GMT

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

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 22:04:57 GMT

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

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 22:36:03 GMT

This?

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

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 22:46:20 GMT

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?

Re: Totals don't add up in Pivot Table

sunny_talwar — Fri, 08 Jul 2016 23:08:41 GMT

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?

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 23:18:07 GMT

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!

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 23:29:00 GMT

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

Re: Totals don't add up in Pivot Table

Fri, 08 Jul 2016 23:37:48 GMT

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,

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

(ooxml, embedded labels, table is DATA);

Dim:

LOAD * INLINE [

Dim

];

Dim2:

LOAD * INLINE [

Dim2

];