8 Replies Latest reply: Aug 10, 2018 4:17 PM by Shamsu MK RSS

    Subtotal-Calculated Dimension

    Shamsu MK

      DearTeam

      Could you please support on the below question?

      Please find attached a report made in Qlik View with the following script. The question is how can I get a subtotal after "6.Provision-employees terminal benefits Nett" ?

       

      IF(Account='9999' ,'1.Profit for the year' ,

      IF(Account='275881' OR Account='275878' OR Account='275883' OR Account='275876' OR Account='275886'OR Account='275873'OR Account='275917' ,  '2.Depreciation' ,

      IF(Account='275890' OR Account='275862', '3.Amortization' ,

      IF(Account='90020' , '4.Provision-doubtful debts' ,

      IF(Account='399303' , '5.Provision-slow moving inventories' ,

      IF(Account='92355' , '6.Provision-employees terminal benefits Nett' ,

      IF(Account='140000' OR Account='93001' OR Account='151015' OR Account='141000' OR Account='91000' OR Account='140000' OR Account='91321' OR Account='199100'

            OR Account='151015' OR Account='275841'OR Account='149990' OR Account='149952' OR Account='149995'OR Account='190000'

            OR Account='149971' ,  '7.Accounts receivables-Trade' ,



      IF(Account='150030' OR Account='150130' OR Account='93000' OR Account='141150' OR Account='154900' OR Account='150040' OR Account='91003'OR Account='93500'

         OR Account='93501' OR Account='157100' OR Account='157110'OR Account='157122' OR Account='199122' OR Account='92123'OR Account='150140'

            OR Account='150120' OR Account='150020'OR Account='156910'OR Account='183170'OR Account='156926'OR Account='183116',  '8.Prepayment and other receivables' ,

           

        

      IF(Account='84000' ,'test' ,'OTH')))))))))

        • Re: Subtotal-Calculated Dimension
          Olivier GAUTIER

          Hi,

           

          you could do this in script with

          accountNb and a field for account class,

          so you can add another field class 1-6 , class 7-9 to make your subtotal

           

          regards

          • Re: Subtotal-Calculated Dimension
            Ahmar Ansari

            Hi Shamu,

             

            Please find below output and code for your requirement.

             

            This is your table like below

             

            Please add one more inline table to achieve your requirement :

             

            load * Inline [

            Heading,Value

            1.Profit for the year,2002

            2.Depreciation,300

            3.Amortization,400

            4.Provision-doubtful debts,100

            5.Provision-slow moving inventories,212

            6.Provision-employees terminal benefits Nett,321

            7.Accounts receivables-Trade,421

            8.Prepayment and other receivables900

            test,10000

            OTH,200000

            ];

             

             

            You have to add below table like below:

             

            LOAD * INLINE [

                Heading_Subtotal, Heading

                1.Profit for the year, 1.Profit for the year

                2.Depreciation, 2.Depreciation

                3.Amortization, 3.Amortization

                4.Provision-doubtful debts, 4.Provision-doubtful debts

                5.Provision-slow moving inventories, 5.Provision-slow moving inventories

                6.Provision-employees terminal benefits Nett, 6.Provision-employees terminal benefits Nett

                Subtotal,1.Profit for the year

                Subtotal,2.Depreciation

                Subtotal,2.Depreciation

                Subtotal,3.Amortization

                Subtotal,4.Provision-doubtful debts

                Subtotal,5.Provision-slow moving inventories

                Subtotal,6.Provision-employees terminal benefits Nett

                7.Accounts receivables-Trade, 7.Accounts receivables-Trade

                8.Prepayment and other receivables900, 8.Prepayment and other receivables900

                test, test

                OTH, OTH

            ];

             

            Output :

            Capture.PNG

             

            Also, Find attached QlikView file for your reference.

             

            Regards

            Ahmar

              • Re: Subtotal-Calculated Dimension
                Shamsu MK

                Thank you. I will check this option also. Please note the subtotal is not correct as per your example.

                Depreciation is duplicating, but still after the rectification total is not matching

                 

                Subtotal,2.Depreciation

                Subtotal,2.Depreciation

                • Re: Subtotal-Calculated Dimension
                  Shamsu MK

                  Hello Ahmar

                  Suppose I make subtotal as Subtotal A, Subtotal B, Subtotal C as mentioned below, the total amounts are correct but it is not appearing in a proper way. Not in the same sorting as the script. what could be the reason ?

                  LOAD * INLINE [

                      Heading_Subtotal, Heading

                      1.Profit for the year, 1.Profit for the year

                      2.Depreciation, 2.Depreciation

                      3.Amortization, 3.Amortization

                      4.Provision-doubtful debts, 4.Provision-doubtful debts

                      5.Provision-slow moving inventories, 5.Provision-slow moving inventories

                      6.Provision-employees terminal benefits Nett, 6.Provision-employees terminal benefits Nett

                      Subtotal A,1.Profit for the year

                      Subtotal A,2.Depreciation

                      Subtotal A,2.Depreciation

                      Subtotal B,3.Amortization

                      Subtotal B,4.Provision-doubtful debts

                      Subtotal B,5.Provision-slow moving inventories

                      Subtotal C,6.Provision-employees terminal benefits Nett

                      7.Accounts receivables-Trade, 7.Accounts receivables-Trade

                      8.Prepayment and other receivables900, 8.Prepayment and other receivables900

                      test, test

                      OTH, OTH

                  ];