topic Re: Variance amount between most recent month and previous month in QlikView

Variance amount between most recent month and previous month

alec1982 — Tue, 27 May 2014 18:40:14 GMT

Hi guys,

I am trying to build a table to show the variance between most recent Month and the previous month of it

Anybody can help

My table looks like

Invoice ID Date Amount

1 03/01/2014 500

2 03/05/2014 200

3 04/07/2014 700

4 04/08/2014 200

Result should be equal to (200+700) - (500+200)=200

Thxs for all your help

maxgro — Tue, 27 May 2014 19:15:44 GMT

perhaps this?

alec1982 — Tue, 27 May 2014 20:11:15 GMT

Hi,

thank you for your help but this doesn't work for me.. I will need to do it on the script side somehow..

best,

Alec

maxgro — Tue, 27 May 2014 20:24:00 GMT

load *, MonthStart(Date) as NewMonth;

load * inline [

InvoiceID, Date , Amount

1 , 03/01/2014 , 500

2 , 03/05/2014 , 200

3 , 04/07/2014 , 700

4 , 04/08/2014 , 200

];

load

NewMonth,

AmountByMonth,

AmountByMonth - Peek(AmountByMonth) as AmountByMonthDiff;

load

NewMonth,

sum(Amount) as AmountByMonth

Resident

group by NewMonth

order by NewMonth;