How to subtract a cell from previous cell in the same field?

[Anonymous]

Hi All,

I'm trying to subtract a cell from previous cell in the same field, I found many good and simple suggestions using peek and previous functions. Unfortunately, those functions do not work in the calculated field expression.

I have a counter that update the table every 15 minutes, the new updates are accumulation of the previous ones, meaning it start with 0, after 1st update, it become 20, then 50 ..etc. I want to add a field, in the managing data section, to display only the difference count after every update. note that i will not have negative values, and in case of no previous count it put the value of that count.

CountTime	Count
01-05-18 0:00	0
01-05-18 0:15	20
01-05-18 0:30	50
01-05-18 0:45	70
01-05-18 1:00	70
01-05-18 1:15	79

Many thanks in advance

spiro

[sunny_talwar]

May be this

RangeSum(Count, -Above(Count))

[Anonymous]

Hi Sunny,

Thanks for your prompt response.

But the suggested string function doesn't work as well.

Please visit the link below for the functions that works in the "calculated field expression":

https://help.qlik.com/en-US/sense/April2018/Subsystems/Hub/Content/LoadData/add-calculated-field.htm

regards

spiro

[sunny_talwar]

Would you be able to share a sample to show the issue?

[Anonymous]

of-course ..... find the attached excel file.

[balabhaskarqlik]

May be this:

ABC:

Load

a,

Count,

c

from abc;

Let Maxvar = peek('Count',1,ABC);

Temp:

Load

a,

Rangesum($(maxvar),-previous(count)) AS COUNTRANGE

c

RESIDENT ABC;

DROP TABLE ABC;