topic Re: Sub Grand Total Placement in QlikView

Sub Grand Total Placement

Anonymous — Thu, 28 May 2015 16:47:38 GMT

Wondering if theres a answer/work around for this question that was previously asked.

Subtotal and Grand Total

Can a sub total be at the top and a Grand total be at the bottom in QV?

Re: Sub Grand Total Placement

Thu, 28 May 2015 17:19:25 GMT

Please see solution in QVW file.

Regards,

Uday.

Re: Sub Grand Total Placement

Anonymous — Thu, 28 May 2015 17:27:14 GMT

Thanks! Will this work for a Pivot table. I should have been more specific. Would I uncheck the Partial Sum box?

Re: Sub Grand Total Placement

petter — Thu, 28 May 2015 17:29:27 GMT

Sort of - just combine two Sheet Objects: One Pivot Table and one Input Box so it could look like this:

Re: Sub Grand Total Placement

petter — Thu, 28 May 2015 18:06:38 GMT

Actually it can be done in a Pivot Table alone - without the help of an Input Box (which I used in the previous suggestion above)....

Re: Sub Grand Total Placement

Anonymous — Thu, 28 May 2015 18:15:30 GMT

awesome this looks like what I need Thanks tons!

Re: Sub Grand Total Placement

petter — Thu, 28 May 2015 18:19:06 GMT

I haven't explained much about the properties I had to set and the expressions ... I will post an explanation within a couple of hours ... listing what needs to be set in the Pivot Chart ...

Re: Sub Grand Total Placement

petter — Thu, 28 May 2015 22:08:40 GMT

0. In your data table you have to create an additional Grand Total row so you have a place holder

to calculate the Grand Total and get it to show in the Pivot Table.

It can be done like this in the load script:

CONCATENATE LOAD 'Grand Total' AS D1, '' AS D2, '' AS D2, '' AS D3, '' AS N AUTOGENERATE 1;

Place it after your main table so it will force concatenate an extra row to the main table.

1. Create A Pivot Table with the three dimensions D1, D2, D3

2. A single expression:

If(Dimensionality()=0,'',

If( D1 = 'Grand Total',

If( Dimensionality() > 1 , Null() , Sum(TOTAL N) )

, Sum(N)

)

3. Presentation tab select Subtotals: Subtotals on Top

4. Style tab: check the check-box Indent Mode and Use Only First Dimension Label

5. Layout tab: Border Width 0 pt

6. Caption remove check on Show Caption

7. For all dimensions check-mark on Supress When Value Is Null

8. On Expression tab - expand the expression on the left and then click Background Color

9. Fill in the Definition (expression): If( D1='Grand Total', RGB(210,210,255))

10. Click on the Text Format and press Edit button

11. Fill in the Definition (expression): If( D1='Grand Total', '<B>')

10. On Dimensions tab: expand the D1 dimension - click on Background Color and then press Edit button and press OK

11. Fill in the same definition as in step 9: If( D1='Grand Total', RGB(210,210,255))

12. click on Text Format and then press Edit button

13. Fill in the same expression as in step 11: If( D1='Grand Total', '<B>') and press OK

That is basically it ... although there are some additional cosmetic steps ... I leave it to you to figure out ...

Re: Sub Grand Total Placement

Anonymous — Fri, 29 May 2015 10:10:45 GMT

Wow love the step by step directions! You Rock

Thanks

Re: Sub Grand Total Placement

Anonymous — Fri, 29 May 2015 11:20:48 GMT

Peter, I just got a chance to look at this - this is my second app that im doing. I have multiple (10ish )amount columns that I need a grand total on. Im not sure how I would change the above code for that. Any suggestions.