Average Daily Difference - Rolling Difference of past history

[#1]

Hi,

I have a requirement to do a Rolling difference of past history.

Example:

Day      Amount      DailyDifference

1           10

2           15                5

3           20                5

4           23                3

5           30                7

Total:                       20

This value to be displayed in a Text Box Daily Difference: = Sum(DailyDifference)

Could anyone kindly suggest how do we do this. Thanks.

Thanks,

Qlikshare

[jonathandienst]

Something like:

=Sum(Aggr(Sum(Amount) - Alt(Above(Sum(Amount)), 0), Day))

[#3]

Thanks. But how do we display this result in Text Box?

If we have a straight table then using Above works fine but the requirement is to display this aggregated figure in a text box.

Kindly suggest.

Thanks,

Qlikshare

[simenkg]

=sum({<Day = {"$(=Max(Day))"}>} Amount) - sum({<Day = {"$(=Min(Day))"}>} Amount)

[#5]

‌Thanks. This would give me difference between max and min date.

But the requirement is to get this subtraction done for all the Dates available One by one.

LIke we do rolling sum of series of months, instead need to do rolling subtraction.

Please suggest.

Thanks,

Qlikshare

[simenkg]

But the answer will always be the same when you aggregate it?

In a text box the sum of month to month change is the last value - the first value so you don't need to do a rolling sum.

[#7]

‌We would need the difference for all the months and not just current and previous month.

Say amount for Jan is 7, Feb is 10, Mar is 11,Apr is 5,May is 21

Say for 2015 June date selection, amount of Jan - Feb - March - Apr - May is required,

which is by taking Absolute,

10-7= 3

11-10 = 1

5-11=6 taking abs

21-5=16

Total is 3+1+6+16= 26

please suggest.

Thanks,

qlikshare

[Digvijay_Singh]

See if script change is the option, it would make things easy -

Table:

Load *, alt(fabs(Amount-peek(Amount)),0) as DailyDifference;

load * inline [

Day,      Amount     

1,           10

2,           15               

3,           20             

4,           23             

5,           30 ];

[#9]

‌Thanks. Tried changing script,at this level of detail of data though it works but when going further levels down it goes different.

Say,

Day  L_Cat   Amount

1       L1         6

1       L2         4

2       L1         3

2       L2         2

In above example, last record, we would need Daily Difference Amount:

ABS(2-4) Subtracting Day2 L2 amt from Day1 L2 amt.

But the issue Is we get ABS(2-3) which is Day2 L2 subtracted from Day2 L1.

Due to peek taking just previously read one Which is in Day 2 record itself, means subtracting Day2 record with Day2 record itself instead of Corresponding Day1.

Whether any modification required in Peek syntax.

Please suggest.

Thanks,

Qlikshare

[Digvijay_Singh · Accepted solution]

For that you need to use order by Category and you can use peek in below form -

Table:

load * inline [

Day, Cat,     Amount     

1,     L1,        10

1,     L2,      15               

2,     L1,      20             

2,     L2,      23             

];

Output:

Load *, if(Cat=Previous(Cat),alt(fabs(Amount-peek(Amount)),0),0) as DailyDifference

Resident Table order by Cat;

Drop Table Table;