Variance amount between most recent month and previous month

[alec1982]

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]

perhaps this?

[alec1982]

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]

s:

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

];

t:

load

NewMonth,

AmountByMonth,

AmountByMonth - Peek(AmountByMonth) as AmountByMonthDiff;

load

NewMonth,

sum(Amount) as AmountByMonth

Resident

s

group by NewMonth

order by NewMonth;