Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gauravgg
New Contributor III

How to Subtract Date from the previous row????

Hi

I have the following table

I have to create a New Column

I have to subtract RowNo 2 from RowNo 1 for which the result should come in new column in format (HH:MMSmiley FrustratedS)

This should be applied for all the rows.

                              TimestampNew Column(HH:MMSmiley FrustratedS)RowNo
08-01-2016 03:001
08-01-2016 03:01(08-01-2016 03:01)-(08-01-2016 03:00)2
08-01-2016 03:063
08-01-2016 03:064
08-01-2016 03:075
08-01-2016 03:076
08-01-2016 03:107
08-01-2016 03:118
08-01-2016 03:179
08-01-2016 03:1910
08-01-2016 03:2311
08-01-2016 03:2312
08-01-2016 03:3113
08-01-2016 03:3414
08-01-2016 03:4915
08-01-2016 03:5116
08-01-2016 03:5417
Tags (2)
5 Replies
MVP
MVP

Re: How to Subtract Date from the previous row????

In the front end of the app, you can use Above or Below function:

Timestamp - Above(Timestamp)

gauravgg
New Contributor III

Re: How to Subtract Date from the previous row????

Hi Sunny

If i an not wrong Timestamp - Above(Timestamp) can be done on the visualisation part

Can this be done in the script ?

MVP
MVP

Re: How to Subtract Date from the previous row????

You can use Peek/Previous in the script to do this. But make sure your data is correctly sorted when you use peek/previous.

Peek() or Previous() ?

gauravgg
New Contributor III

Re: How to Subtract Date from the previous row????

it would be help full , if you share script

or Share .qvf

MVP
MVP

Re: How to Subtract Date from the previous row????

Something like this:

SET TimestampFormat='MM-DD-YYYY hh:mm';

Table:

LOAD Timestamp,

  RowNo

FROM

[https://community.qlik.com/thread/239834]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD RowNo,

  Timestamp,

  Interval(Timestamp - Previous(Timestamp), 'hh:mm:ss') as NewColumn

Resident Table

Order By RowNo;

DROP Table Table;