Variance Between Days

[rido1421]

Hi There

I have a Date field and an Amount field

[Date],

[Amount]

I would like to create a variance between each day?

And then also in percentage.

What is the easiest way to do this?

Date	Amount	Variance
2015/01/01	200	0
2015/01/02	300	100
2015/01/03	500	200
2015/01/04	200	300
2015/01/05	400	200
2015/01/06	100	300
2015/01/07	300	200
2015/01/08	400	100

Thanks

[v_iyyappan · Accepted solution]

Hi,

PFA

Regards,

[avinashelite]

Hi Rido,

Try like this in the expression:

If(Rowno()=1,0,Previous(amount)-amount)

or

If(Rowno()=1,0,above(amount)-amount)

[jagan]

Hi,

Try like this

In script

LOAD

*,

If(Rowno()=1,0,amount - Previous(amount)) AS Variance,

If(Rowno()=1,0,(amount - Previous(amount)) /amount AS Variance%,

FROM TableName

ORDER BY Date;

In chart:

If(Rowno()=1,0,amount - above(amount))

Hope this helps you.

Regards,

Jagan.

[Anonymous]

Hi Rido,

Try as below

In script:

Amount:
LOAD * Inline
[
Date,Amount
2015/01/01,200
2015/01/02,300
2015/01/03,500
2015/01/04,200
2015/01/05,400
2015/01/06,100
2015/01/07,300
2015/01/08,400
];

NoConcatenate

AmountVariance:
LOAD
Date,
Amount,
If(rowno()=1,0,Amount-Previous(Amount)) as AmountVariance,
If(rowno()=1,0,(Amount-Previous(Amount)) /Amount*100) AS AmountVariance%
Resident Amount Order By Date asc;

In chart:

Sort tab: Mark only Load order check box

Regards

Neetha

[MarcoWedel]

Alt(Amount-Previous(Amount),0)