Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. April 17 - 20, 2023, in Las Vegas! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
nsnybs21qv
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
maxgro
MVP
MVP

RESULT

1.jpg

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;

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand
nsnybs21qv
Contributor III
Contributor III
Author

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

Gysbert_Wassenaar

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;


talk is cheap, supply exceeds demand