12 Replies Latest reply: May 16, 2012 11:31 AM by user5674 RSS

    Pivot Table: total row aggregated over dimension

      Hello,

       

      I have attached an application to explain my problem.

       

      I want a pivot table as below with a final row containing the two totals values of expressions (1,2,3) aggregated by the "Measure" dimension.

      The measure dimension is a inline table without connexion with data. i just use it for have a "label dimension" in order to use two formula by expression dependant of the "Measure" label.

       

      The table I want.

      CategoryMeasureExpr1Expr2Expr3
      C1MeasureA202
      MeasureB0100001,4100001,4
      C2MeasureA101
      MeasureB000
      C3MeasureA101
      MeasureB0555555
      C4MeasureA000
      MeasureB000
      TotalMeasureA404
      MeasureB0100556,4100556,4

       

      The one I obtain using "partial sum":

       

      CategoryMeasureExpr1               Expr2               Expr3
      C1MeasureA202
      MeasureB0100001,4100001,4
      Total002
      C2MeasureA101
      MeasureB000
      Total001
      C3MeasureA101
      MeasureB0555555
      Total001
      C4MeasureA000
      MeasureB000
      Total000

       

      The total values must be processed when we do fields selection, so I can't process the totals values in the load script.

       

      The only method I have found to have this is to use two pivot tables: one for the total, one for other rows.

       

      But I want only one pivot table.

       

      Could you help me?

        • Re: Pivot Table: total row aggregated over dimension
          Deepak Vadithala

          Hi,

           

          I wanted to make sure if I have understood your question. Do you want to show the Sub-totals for each Measure (Field)? I mean in your example do you wanted to see Total for each Category where the calculation is MeasureA + MeasureB, is this right?

           

          If this is incorrect assumption then please can you quickly mockup a table showing what is the output you are expecting.

           

          Thanks,

          DV

            • Re: Pivot Table: total row aggregated over dimension

              Hi,

               

              I want on "Total - MeasureA" the total of MeasureA rows, same thing for B.

               

              For example:

              Expr1 we have MeasureA  C1->2,  C2->1, C3->1 and C4->0. So on "Total - MeasureA"  I want the sum : 2+1+1+0=4

              and on "Total - MeasureB" : 0+0+0+0=0

               

              I want Total MeasureA over dimension Category, and the same for MeasureB as in my first table.

               

              Thanks.

                • Re: Pivot Table: total row aggregated over dimension
                  Deepak Vadithala

                  Hi Again,

                   

                  Now I understand the requirement. Your "Total Temp Solution" table was hidden in your original example and that's the reason why I couldn't follow you completely. Anyway, I had to tweak two things to get this working:

                   

                  1. I have added "Total" value in the Category field in the Load Script. After reloading the document, it shows up "Total" at the end under Category Dimension and you can also remove it by using calculated expression if it's not required in other objects.

                   

                  2. I don't like to use IF statements because it's massive overhead when your data fields have high cardinality. However, this still works as a solution and I'll leave it your judgement.

                  _____________________________________________________________

                  Here is the expression you should be using :

                  IF(Measure='MeasureA' and Category <> 'Total',

                      Count(Distinct Id),

                  IF(Measure='MeasureA' and Category = 'Total',

                      sum(TOTAL AGGR(COUNT(Distinct Id), Category)),

                  0

                     )  

                  )

                  _____________________________________________________________

                   

                  Explanation of above expression :


                  Step 1 : We are checking both the condition i.e (If the Measure = 'MeasureA' and Category <> 'Total' then we will use your original expression which is :

                  Count(Distinct Id)


                  Step 2 : Once the QV engine evaluates "False" for the above expression then we are checking the condition i.e. (If the Measure = 'MeasureA' and Category = 'Total' then we will use  this expression :

                  Sum(TOTAL AGGR(COUNT(Distinct Id), Category))

                  This expression aggregates and calculates the total. Which is 4 in your example.

                   

                  Step 3: If both the conditions are not "True" then QV assigns 0. So all the values with Measure = 'MeasureB' having 0.

                   

                  I'm also attaching the QV document for your reference.

                   

                  I hope this helps!

                   

                  Good luck!

                   

                  Cheers,

                  DV

                   

                  www.QlikShare.com

                    • Re: Pivot Table: total row aggregated over dimension

                      Thank you!

                       

                      It seems a quite good solution.

                       

                      I have just added this to my load script in order to not disturb other objects using "Category" field without totals:

                       

                      SET NULLINTERPRET=NULL;

                      CategoryMap:
                      LOAD Category, Category as CategoryMap
                      resident Category;
                      Concatenate(CategoryMap)
                      Load * INLINE [
                      Category,CategoryMap
                      NULL,Total
                      ];
                      SET NULLINTERPRET=;

                       

                      I use only for my table with total line the field "CategoryMap" instead of field "Category", and it's perfect.


                       

                       

                       

                       

                        • Re: Pivot Table: total row aggregated over dimension
                          Deepak Vadithala

                          I'm happy to know that you have got it working. It was interesting question, which made me think. Thanks for that

                           

                          Not sure if you have massive application. But if you have massive application then you could do Partial Reload for these kind of scenarios. This is ideal situation to use Partial Reload. Incase, if you wanted more understanding on Partial Reload then please check the below post on my blog :

                           

                          http://qlikshare.com/370

                           

                          Have a great weekend.

                           

                          Cheers - DV

                            • Pivot Table: total row aggregated over dimension
                              Giampiero Cina

                              Hi DV,

                              I was looking for your solution because it is very interesting.

                              May I ask you a thing!?

                              Your solution with MeasureA works perfectly.

                              What about MeasureB? I used your solution with MeasureB but i don't obtain the result 100556,4 .

                              Am I making a mistake?

                              I used:

                               

                              IF(Measure='MeasureB' and Category <> 'Total',

                                  Count(Distinct Id),

                              IF(Measure='MeasureB' and Category = 'Total',

                                  sum(TOTAL AGGR(COUNT(Distinct Id), Category)),

                              0

                                 )  

                              )

                              I'm sorry but your solution is very nice and I would like to use it.

                              Thanks a lot

                              Giampiero

                                • Re: Pivot Table: total row aggregated over dimension
                                  Deepak Vadithala

                                  Hi Giampiero,

                                   

                                  I'm glad that you find this thread interesting and don't have to be sorry. This whole purpose of having this community is to help each other. I learn a lot from all of you guys. So it's okay mate.

                                   

                                  I'm assuming that you are talking about Expr2. And the original expression for Exp2 is different from Expr1. So please try the below expression and it should work :

                                   

                                  IF(Measure='MeasureB' and Category <> 'Total',

                                      SUM( aggr(NODISTINCT    Min(ValueB),Category,Id)),

                                  IF(Measure='MeasureB' and Category = 'Total',

                                      SUM(total aggr(NODISTINCT    Min(ValueB),Category,Id)),

                                  0

                                     )  

                                  )

                                   

                                  I'm also attaching the example for your reference.

                                   

                                  Cheers,

                                  DV

                                  www.QlikShare.com

                                    • Pivot Table: total row aggregated over dimension
                                      Giampiero Cina

                                      Hi DV,

                                      thank you so much.

                                      I think the same thing about this community.

                                      I often try to help and to be helped :-)

                                      You're right, I didn't use the expression 2. Now it works perfectly.

                                      I like a lot your solution. It will be a good start point to solve similar problem in the future.

                                      Again, THANK YOU SO MUCH.

                                      Giampiero

                                      • Re: Pivot Table: total row aggregated over dimension

                                        I had just a little problem because on Category field value selections I lost my Total row.

                                        To solve it I just replace my previous inline table like this:

                                         

                                        SET NULLINTERPRET=NULL;

                                        CategoryMap:
                                        LOAD Category, Category as CategoryMap
                                        resident Category;
                                        Concatenate(CategoryMap)
                                        Load * INLINE [
                                        Category,CategoryMap
                                        NULL,Total
                                        ];
                                        SET NULLINTERPRET=;

                                         

                                        By just a list independent of data selections

                                        CategoryMap :

                                        LOAD Category as CategoryMap

                                        resident Category;

                                        Concatenate(CategoryMap)

                                        Load * INLINE [

                                        CategoryMap

                                        Total

                                        ];

                                         

                                        Then use this field CategoryMap in pivot table first dimension expression as below:

                                        =If(CategoryMap=Category or CategoryMap='Total',CategoryMap)

                                        So the total row don't be hidden if I select C1 and C2 Category value, for example.

                                         

                                        And as the "Total" value isn't linked to Category field values expressions are automatically set to total value without the

                                        If(CategoryMap='Total') condition. I have just need to set this condition on total value for expression using AGGR function, not for simple Sum and Count expression.

                                        My row Total isn't hidden now when I do selection  on field "Category".

                                         

                                        I just see something strange now: why C1 Exr2 measureB is 100001.4 and not 100000.95 ?

                                        min valueB for  Id 1 and Category C1 is 0.45 and for Id 9 Category C1 it's 100000.5

                                        Normally the sum must be 100000.95, not 100001.4

                                         

                                         

                                        I have another question, but without link with this subject:

                                        Is it possible to do something like this on this pivot table?:

                                        MyFirstSuperHeaderMySecondSuperHedaer
                                        CategoryMapMeasureExpr1Expr2Expr3
                                        C1MeasureA202
                                        MeasureB0100001,4100001,4
                                        MeasureA101
                                        C2MeasureB000
                                        C3MeasureA101
                                        MeasureB0555555
                                        C4MeasureA000
                                        C4MeasureB000
                                        TotalMeasureA300
                                        MeasureB0100556,40

                                        Currently I made this with two horrible text object which don't autosize when my pivot table size change.

                                        I have already tried to do some sort of inline table with headers/super headers, but I can't hide/delete column when expressions is null. I had something like this

                                        Parent Header 1Parent Header 2
                                        Expr1Expr2Expr3Expr2Expr3

                                        even with "Supress When Value is Null" checked on my "inline" dimension ParentHeader and Expressions defined with a condition like

                                        IF(ParentHeader='ParentHeader1',***) I have all expressions on each of my horizontal dimension values.

                                          • Re: Pivot Table: total row aggregated over dimension

                                            Hello,

                                             

                                            Could someone help me on this 2 subjects above:

                                            • Incorrect expression values on my table
                                            • Super headers for specific expressions.

                                             

                                             

                                            The first point is the most important. I tried some formula but it doesn't fix it. Perhaps I use bad syntax?

                                            My sum expression don't display the good result.

                                             

                                            Example attached.

                                              • Re: Pivot Table: total row aggregated over dimension

                                                I have finaly found a formula which give me the good result.

                                                I don't know if there are better formulas to do this.

                                                 

                                                But now I want my "total" Category field value remain when I do selection on the field Category (directly on the table or by a selection list)

                                                 

                                                In the application attached here, I have two table (aggregate 1 & 2).

                                                 

                                                If I select Category values by the list, I have always the good total value on my table aggregate1, but not on the second, aggregate2.

                                                If I select directly on the table aggregate1 dimension, I lose the total rows.

                                                 

                                                On the second table I lose always the total values when I do selections.

                                                 

                                                Could you help me? I want the total row remain on my pivot when I select Category field values.

                                                 

                                                Thanks.