
Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 8, 2016 2:11 PM (in response to Vish Pattanashetty)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
Vish Pattanashetty Jul 8, 2016 4:02 PM (in response to Sunny Talwar )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 Jul 8, 2016 4:04 PM (in response to Vish Pattanashetty)Replace this with your current measure

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 8, 2016 4:14 PM (in response to Sunny Talwar )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]), 'MMMYY')
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, '1630 Days',If (([Max Days to Submit]) <61, '3160 Days',If (([Max Days to Submit]) < 91, '6190 Days','90+ Days' ) )))
Thanks for your help

Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 8, 2016 4:47 PM (in response to Vish Pattanashetty)You might have to move your calculated dimensions into the script.
LOAD Date(MonthStart([Approved Date]), 'MMMYY') as MonthYear,
If (([Max Days to Submit]) < 16 or IsNull([Max Days to Submit]),' Within Policy',If (([Max Days to Submit]) < 31, '1630 Days',If (([Max Days to Submit]) <61, '3160 Days',If (([Max Days to Submit]) < 91, '6190 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
Vish Pattanashetty Jul 8, 2016 4:55 PM (in response to Sunny Talwar )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
Vish Pattanashetty Jul 8, 2016 4:59 PM (in response to Vish Pattanashetty)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 Jul 8, 2016 5:16 PM (in response to Vish Pattanashetty)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
Sunny Talwar Jul 8, 2016 5:16 PM (in response to Vish Pattanashetty)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
Vish Pattanashetty Jul 8, 2016 5:21 PM (in response to Sunny Talwar )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 Jul 8, 2016 5:30 PM (in response to Vish Pattanashetty)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
Vish Pattanashetty Jul 8, 2016 5:47 PM (in response to Sunny Talwar )Let me send you the sample. Don't give up on me. :)

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 8, 2016 6:04 PM (in response to Vish Pattanashetty)Sunny, please see attached. I need the totals in the pivot to come down and to the right. Thanks for your help!

test2.xlsx 11.2 K

Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 8, 2016 6:36 PM (in response to Vish Pattanashetty)
Community_224351.qvf 176.0 K

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 8, 2016 6:46 PM (in response to Sunny Talwar )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 Jul 8, 2016 7:08 PM (in response to Vish Pattanashetty)Yes, it does
I am not sure how you can get 2 there. There are 3 unique report which fall under 1630 bucket and are in Oct. Why won't you see 3 there?

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 8, 2016 7:18 PM (in response to Sunny Talwar )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
Vish Pattanashetty Jul 8, 2016 7:29 PM (in response to Vish Pattanashetty)Where do you define the 'Total'? How does it know to calculate the summation?

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 8, 2016 7:37 PM (in response to Vish Pattanashetty)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]), 'MMMYY')as Months,
If (([Max Days to Submit]) < 16 or IsNull([Max Days to Submit]),' Within Policy',If (([Max Days to Submit]) < 31, '1630 Days',If (([Max Days to Submit]) <61, '3160 Days',If (([Max Days to Submit]) < 91, '6190 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
];

Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 9, 2016 8:12 AM (in response to Vish Pattanashetty)Script looks good to me. How does your dimension and expression look like?

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 11, 2016 12:53 PM (in response to Sunny Talwar )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
Sunny Talwar Jul 11, 2016 4:01 PM (in response to Vish Pattanashetty)How about this as the expression:
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
Vish Pattanashetty Jul 11, 2016 4:07 PM (in response to Sunny Talwar )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
Sunny Talwar Jul 11, 2016 4:10 PM (in response to Vish Pattanashetty)How about this:
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
Vish Pattanashetty Jul 11, 2016 4:21 PM (in response to Sunny Talwar )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
Sunny Talwar Jul 11, 2016 4:23 PM (in response to Vish Pattanashetty)It is a sum total.... not sure I understand... can you explain with numbers

Re: Totals don't add up in Pivot Table
Vish Pattanashetty Jul 11, 2016 4:32 PM (in response to Sunny Talwar )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
Vish Pattanashetty Jul 12, 2016 2:21 PM (in response to Vish Pattanashetty)
Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 13, 2016 8:04 PM (in response to Vish Pattanashetty)I will look at this again today, have not forgotten you my friend

Re: Totals don't add up in Pivot Table
Sunny Talwar Jul 13, 2016 8:07 PM (in response to Sunny Talwar )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)))
The expression which finally worked was this, right?
"You might have to move your calculated dimensions into the script.
LOAD Date(MonthStart([Approved Date]), 'MMMYY') as MonthYear,
If (([Max Days to Submit]) < 16 or IsNull([Max Days to Submit]),' Within Policy',If (([Max Days to Submit]) < 31, '1630 Days',If (([Max Days to Submit]) <61, '3160 Days',If (([Max Days to Submit]) < 91, '6190 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
Vish Pattanashetty Jul 13, 2016 8:17 PM (in response to Sunny Talwar )Here is what has worked so far. (With Totals at the top)
[DATA]:
LOAD
[Type],
[Approved Date],
[Max Days to Submit],
[Unique Report],
Date(MonthStart([Approved Date]), 'MMMYY')as Months,
If (([Max Days to Submit]) < 16 or IsNull([Max Days to Submit]),' Within Policy',If (([Max Days to Submit]) < 31, '1630 Days',If (([Max Days to Submit]) <61, '3160 Days',If (([Max Days to Submit]) < 91, '6190 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
];
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
Sunny Talwar Jul 8, 2016 8:36 PM (in response to Vish Pattanashetty)Script:
Table:
LOAD Date(MonthStart([Approved Date]), 'MMMYY') as MonthYear,
If(Days < 16 or IsNull(Days),' Within Policy',
If(Days < 31, '1630 Days',
If(Days <61, '3160 Days',
If(Days < 91, '6190 Days', '90+ Days' )))) as Bucket,
[Unique Report];
LOAD * INLINE [
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:
LOAD * INLINE [
Dim
1
2
];
Dim2:
LOAD * INLINE [
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

Community_224351.qvf 176.0 K
