Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi.
I have the folllowing chart.
Hour | Current |
9 | 54.749 |
10 | 141.743 |
11 | 269.626 |
12 | 419.151 |
13 | 581.241 |
14 | 722.507 |
15 | 841.649 |
16 | 936.237 |
17 | 1.018.161 |
18 | 1.105.595 |
19 | 1.212.814 |
20 | 1.381.175 |
21 | 1.553.628 |
22 | 1.571.625 |
How can i subtract the Hour 22 from the Hour 21, the Hour 21 from the Hour 20 and e.t.c in a chart to have the following results ?
9 | 86.993 |
10 | 127.884 |
11 | 149.524 |
12 | 162.090 |
13 | 141.266 |
14 | 119.142 |
15 | 94.588 |
16 | 81.925 |
17 | 87.434 |
18 | 107.219 |
19 | 168.360 |
20 | 172.454 |
21 | 17.996 |
Thank you in advance
Try chart inter record functions like Below():
Create your chart with dimension Hour and use as expression:
=Below(Sum(Current)) - sum(Current)
May be like this in the script:
Table:
LOAD Hour,
Current
FROM
[https://community.qlik.com/thread/218744]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD Hour,
Current,
Num(Peek('Current') - Current) as Diff
Resident Table
Order By Hour desc;
DROP Table Table;
I should stay awake, even on sunday...
I found this formula and i have the following results
[Current]-above([Current])
Hour | Current | Diff |
9 | 67.509 | - |
10 | 164.980 | 97.471,52 |
11 | 297.143 | 132.162,57 |
12 | 447.743 | 150.599,89 |
13 | 593.142 | 145.399,48 |
14 | 723.848 | 130.706,02 |
15 | 834.814 | 110.965,57 |
16 | 925.865 | 91.051,28 |
17 | 996.620 | 70.754,97 |
18 | 1.076.057 | 79.436,44 |
19 | 1.177.834 | 101.777,55 |
20 | 1.307.128 | 129.294,29 |
21 | 1.449.573 | 142.444,75 |
22 | 1.462.159 | 12.585,81 |
Do not follow my first post (I deleted it because it was wrong. Sorry). Follow Stefan's suggestion.
Best,
Peter
I am going to offer a slight modification to Stefan's solution (and I am sure he would approve the changes I propose)
(Below(Sum({1}Current)) * Avg(1)) - Sum(Current)
or
(Below(Sum({<Hour>}Current)) * Avg(1)) - Sum(Current)
This will help if you plan to make selection in the Hour field.
When no selections are made:
When single hour is selected:
When hours are selected continuously (14,15,16,17):
When non continuous hours are selected (13, 15, 17):
So if you don't plan to make selections in hour field, you might be good with what Stefan provided. But if you plan to make selection in Hour, then you might want to make the above modifications.