Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

shamsu_mk
Contributor

Subtotal-Calculated Dimension

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

2 Solutions

Accepted Solutions
shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

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

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

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

];

8 Replies
ogautier62
Valued Contributor II

Re: Subtotal-Calculated Dimension

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

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

Thank you. It was in my mind but i have a doubt that if I do this my presentation layout will be changed as attached?Capture.JPG

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

Is that possible to give a sample qvw file ?

ogautier62
Valued Contributor II

Re: Subtotal-Calculated Dimension

so what do you want exactly as result ?

if you add new field as dimension you'll have your subtotal

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

Fine. I will try this and update you the status. Thank you

ahmar811
Contributor III

Re: Subtotal-Calculated Dimension

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

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

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

shamsu_mk
Contributor

Re: Subtotal-Calculated Dimension

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

];

Community Browser