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

[ogautier62]

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]

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?

[shamsu_mk]

Is that possible to give a sample qvw file ?

[ogautier62]

so what do you want exactly as result ?

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

[shamsu_mk]

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

[ahmar811]

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 :

Also, Find attached QlikView file for your reference.

Regards

Ahmar

[shamsu_mk · Accepted solution]

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 · Accepted solution]

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

];