topic Re: Subtotal-Calculated Dimension in QlikView

Subtotal-Calculated Dimension

shamsu_mk — Wed, 25 Nov 2020 16:16:04 GMT

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

ogautier62 — Tue, 24 Jul 2018 13:19:28 GMT

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

shamsu_mk — Wed, 25 Jul 2018 07:00:36 GMT

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?

Re: Subtotal-Calculated Dimension

shamsu_mk — Wed, 25 Jul 2018 07:01:19 GMT

Is that possible to give a sample qvw file ?

Re: Subtotal-Calculated Dimension

ogautier62 — Wed, 25 Jul 2018 07:04:56 GMT

so what do you want exactly as result ?

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

Re: Subtotal-Calculated Dimension

shamsu_mk — Wed, 25 Jul 2018 07:08:10 GMT

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

Re: Subtotal-Calculated Dimension

ahmar811 — Wed, 25 Jul 2018 07:36:55 GMT

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,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

Re: Subtotal-Calculated Dimension

shamsu_mk — Wed, 25 Jul 2018 12:29:52 GMT

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

Re: Subtotal-Calculated Dimension

shamsu_mk — Fri, 10 Aug 2018 20:17:33 GMT

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

];