32 Replies Latest reply: Jul 13, 2016 8:17 PM by Vish Pattanashetty

# 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.

stalwar1 can you help?

• ###### Re: Totals don't add up in Pivot Table

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

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

Replace this with your current measure

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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?

• ###### Re: Totals don't add up in Pivot Table

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

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

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

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

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

Let me send you the sample. Don't give up on me. :-)

• ###### Re: Totals don't add up in Pivot Table

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

This?

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

• ###### Re: Totals don't add up in Pivot Table

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

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

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

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

• ###### Re: Totals don't add up in Pivot Table

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

[DATA]:

[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:

Dim

1

2

];

Dim2:

Dim2

1

2

];

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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.

• ###### Re: Totals don't add up in Pivot Table

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"

• ###### Re: Totals don't add up in Pivot Table

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

• ###### Re: Totals don't add up in Pivot Table

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.

• ###### Re: Totals don't add up in Pivot Table

We are almost there stalwar1

Don't give up on me now.

Thanks for all your help so far.

• ###### Re: Totals don't add up in Pivot Table

I will look at this again today, have not forgotten you my friend

• ###### Re: Totals don't add up in Pivot Table

Can you let me know the reason for using IsNull() here?

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

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

• ###### Re: Totals don't add up in Pivot Table

Here is what has worked so far. (With Totals at the top)

[DATA]:

[Type],

[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:

Dim

1

2

];

Dim2:

Dim2

1

2

];

And the measure has the following formula

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

• ###### Re: Totals don't add up in Pivot Table

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];

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:

Dim

1

2

];

Dim2:

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

Also, you will need a sort expression

Dimension1 -> Dim

Dimension2 -> Dim2