7 Replies Latest reply: Dec 20, 2012 4:30 AM by alka1988 RSS

    Sub Total Issues

      Hi Guys,
      Hope you can help me figure out how to display my sub-total correctly or as per my requirement:
      Supposed i have the following table:
      MonthGroupYearQuantityAmount
      JANG1201210100
      JANG1201310100
      JANG2201210100
      JANG2201310100
      TOTAL40400
      The above us the total of Qlikview (Default)
      But what I want is to include subtotal per year like below:
      MonthGroupYearQuantityAmount
      JANG1201210100
      JANG1201310100
      JANG2201210100
      JANG2201310100
      TOTAL201220200
      TOTAL201320200

       

       

      Is this possible? Then after this the Grand Total should also have 2012 and 2013...

       

      Thanks in advance...

       

      I also attached the QVW if anyone wanted to play along with...

       

      ~skip

        • Re: Sub Total Issues
          Miguel Angel Baeyens de Arce

          Hi,

           

          You can add a new Show Partial Sums in the Presentation tab of the chart properties to the field FYEAR, or create a new expression, but that will make your chart different. Subtotals are referred to dimension values, you cannot mix them, or "insert a new line" as the expressions make sense related to the dimensions.

           

          Hope that helps.

           

          Miguel

          • Re: Sub Total Issues
            Gysbert Wassenaar

            Try adding the totals to the table:

             

            T1:

            LOAD [Item Final Group],

                 FYEAR,

                 AMOUNT,

                 QTY,

                 MONTH

            FROM YOY_TOTAL.QVD (qvd);

             

            Concatenate

            load 'TOTAL' as [Item Final Group], 'TOTAL' as MONTH, FYEAR, sum(AMOUNT) as AMOUNT, sum(QTY) as QTY

            Resident T1 group by FYEAR;

             

            See attached example.

              • Re: Sub Total Issues

                Hi Gysbert,

                 

                thank you for your reply, this is actually what Im doing but i can't always redo this and then i can no longer use the builtin sub total since it will give me incorrect information for the GRAND total.

                  • Re: Sub Total Issues
                    Gysbert Wassenaar

                    I don't understand what you mean. The totals are recalculated on every reload so they are always correct.  Just don't use the grand total in the chart (disable partial sums on the first dimension) since you've already calculated your own in the script.

                     

                    I'm afraid it's either this solution or something far more messy (if anything at all). As Miguel explained you can't mix subtotals the way you want.

                      • Re: Sub Total Issues

                        Hi Gysbert,

                         

                        The thing is for every summary that the user want I need to create each of them separate chart/pivot table.

                        Becuase if the user wanted to select only particular month then my computed grand total on reload will already be wrong.

                         

                        For illustration purposes please see attached screenshots.

                         

                        Thanks again and I think I will just leave with my current workaround.

                         

                        Regards,

                         

                        ~skip

                          • Re: Sub Total Issues

                            hi !!

                             

                            I donnw Whethr i have understood this completely...and can't see ur Qvw file as m using my personal edition........but if its only regarding getting the subtotals on yearly basis....then y not try pivoting the chart and shifting the Year column towards the left and the Group column towards right

                             

                            Then the subtotals can be seen on Yearly basis if you take year as dimension and the grand total too as you also have mnth as dimension.

                             

                            this was jsst a simple suggession, ignore it.. if the desired result is smthng different.