Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

subtract value from one row after according to hours

Hi, I have a time variable displaying distinct hours and another variable displaying cost(so there is 1 cost value per hour). How can I calculate the difference such that for eg. the cost for 09:00 minus 08:00 and this applies to the rest of the hours? wanted output below.

timecostdifference
00:00:009283649-6300151
01:00:002983498-948511
02:00:00203498718163243
03:00:002019823078177757
04:00:0098375987-97552219
05:00:008237682049700
06:00:0028734685400180
07:00:008273648964200
08:00:009237848-973284
09:00:008264564-907825
10:00:007356739-4573091
11:00:0027836481113920
12:00:003897568

Thanks.

3 Replies
MVP
MVP

Re: subtract value from one row after according to hours

Rachel,

I think your request would attract more attention by setting the status to 'unanswered' instead of 'assumed answered'.

Coming to your question:

You can use inter record function like PEEK() to do what you want, you just need to take care of the input records coming in correctly sorted:

INPUT:

LOAD time,

          cost

FROM

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

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

RESULT:

LOAD time,

          cost,

         peek(cost)- cost as difference

RESIDENT INPUT

ORDER BY time desc;

drop table INPUT;

Re: subtract value from one row after according to hours

swuehl‌ this seems like a post from Aug, 2014 and that's why it seems that Rachel may have changed it to assumed answer

MVP
MVP

Re: subtract value from one row after according to hours

Right, haven't noticed that.

It popped up again in the content stream tonight..