topic Re: Using script to calculate the difference between two rows in QlikView

Using script to calculate the difference between two rows

Anonymous — Wed, 25 Feb 2015 22:23:08 GMT

Hi,

I'm building a financial dashboard that needs to show variance between budget and estimate for each product each month, and want to do the calculation in script.

My file looks like this

Product	Year	Month	Type	Amount
Product 1	2015	Jan	Bud	10,000
Product 1	2015	Jan	Est	9,000
Product 1	2014	Dec	Bud	10,000
Product 1	2014	Dec	Est	8,000
Product 2	2015	Jan	Bud	10,000
Product 2	2015	Jan	Est	7,000
Product 2	2014	Dec	Bud	10,000
Product 2	2014	Dec	Est	6,000

I want the another table look like this

Product	Year	Month	Type	Amount
Product 1	2015	Jan	Var	(1,000)
Product 1	2014	Dec	Var	(2,000)
Product 2	2015	Jan	Var	(3,000)
Product 2	2014	Dec	Var	(4,000)

Does anyone know how to write a script for that?

Thanks

Zixiao

Re: Using script to calculate the difference between two rows

maxgro — Thu, 26 Feb 2015 19:48:00 GMT

RESULT

SCRIPT

source:

LOAD Product,

Year,

Month,

Type,

Amount

FROM

[http://community.qlik.com/thread/154174]

(html, codepage is 1252, embedded labels, table is @1);

final:

load

Product,

Year,

Month,

//Type,

Amount as Amount_Bud

Resident source

Where Type = 'Bud';

join (final) load

Product,

Year,

Month,

//Type,

Amount as Amount_Est

Resident source

Where Type = 'Est';

DROP Table source;

Left join (final)

load

Product,

Year,

Month,

'Var' as Type,

Amount_Est - Amount_Bud as Amount

Resident

final;

Re: Using script to calculate the difference between two rows

Gysbert_Wassenaar — Thu, 26 Feb 2015 19:52:00 GMT

I wouldn't create an additional table, but simply concatenate the result to the existing table:

Data:
LOAD * FROM sometable;

concatenate(Data)

LOAD * WHERE len(trim(Amount))>0;
LOAD
     Product,
     Year,
     Month,
     'Var' as Type,
     if(Type = 'Est', rangesum(Amount,-previous(Amount))) as Amount
RESIDENT Data
ORDER BY Product, Year, Month;

Re: Using script to calculate the difference between two rows

Anonymous — Thu, 26 Feb 2015 20:01:31 GMT

Thanks Gysbert for your reply. One follow up question, for each product, each month, I only listed out budget and estimate as type in this question just to make it easier to explain. However, it actually also has Actual and PriorEstimate as type as well. In this case, I think I need to modify the "if( Type = 'Est', rangesum (Amount, - previous(Amount)))" part of the code right? How should I modify it?

Thanks!

Zixiao

Re: Using script to calculate the difference between two rows

Gysbert_Wassenaar — Thu, 26 Feb 2015 20:05:11 GMT

Add a where clause to restrict the second load to only Est and Bud type records:

Data:
LOAD * FROM sometable;

concatenate(Data)

LOAD * WHERE len(trim(Amount))>0;
LOAD
    Product,
    Year,
    Month,
    'Var' as Type,
    if(Type = 'Est', rangesum(Amount,-previous(Amount))) as Amount
RESIDENT Data
WHERE match(Type,'Est','Bud')
ORDER BY Product, Year, Month;