4 Replies Latest reply: Feb 26, 2015 3:05 PM by Gysbert Wassenaar

# Using script to calculate the difference between two rows

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

RESULT

SCRIPT

source:

Year,

Month,

Type,

Amount

FROM

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

final:

Product,

Year,

Month,

//Type,

Amount as Amount_Bud

Resident source

Where Type = 'Bud';

Product,

Year,

Month,

//Type,

Amount as Amount_Est

Resident source

Where Type = 'Est';

DROP Table source;

Left join (final)

Product,

Year,

Month,

'Var' as Type,

Amount_Est - Amount_Bud as Amount

Resident

final;

• ###### Re: Using script to calculate the difference between two rows

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

```Data:

concatenate(Data)

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

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

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

```Data:

concatenate(Data)

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