Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
time | cost | difference |
00:00:00 | 9283649 | -6300151 |
01:00:00 | 2983498 | -948511 |
02:00:00 | 2034987 | 18163243 |
03:00:00 | 20198230 | 78177757 |
04:00:00 | 98375987 | -97552219 |
05:00:00 | 823768 | 2049700 |
06:00:00 | 2873468 | 5400180 |
07:00:00 | 8273648 | 964200 |
08:00:00 | 9237848 | -973284 |
09:00:00 | 8264564 | -907825 |
10:00:00 | 7356739 | -4573091 |
11:00:00 | 2783648 | 1113920 |
12:00:00 | 3897568 |
Thanks.
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;
swuehl this seems like a post from Aug, 2014 and that's why it seems that Rachel may have changed it to assumed answer
Right, haven't noticed that.
It popped up again in the content stream tonight..