Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

mahinkle
New Contributor III

Sub Grand Total Placement

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Sub Grand Total Placement

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

2015-05-28 #2.PNG

View solution in original post

9 Replies
Not applicable

Re: Sub Grand Total Placement

Please see solution in QVW file.

Regards,

Uday.

mahinkle
New Contributor III

Re: Sub Grand Total Placement

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

MVP
MVP

Re: Sub Grand Total Placement

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

2015-05-28 #1.PNG

MVP
MVP

Re: Sub Grand Total Placement

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

2015-05-28 #2.PNG

View solution in original post

mahinkle
New Contributor III

Re: Sub Grand Total Placement

awesome this looks like what I need   Thanks tons!

MVP
MVP

Re: Sub Grand Total Placement

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

Highlighted
MVP
MVP

Re: Sub Grand Total Placement

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

mahinkle
New Contributor III

Re: Sub Grand Total Placement

Wow love the step by step directions!   You Rock

Thanks

mahinkle
New Contributor III

Re: Sub Grand Total Placement

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.