Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a problem. I need valculate the dynamics up to 7 days back. Example: when we have 01/06/ 2022, I need to calculate the dynamics until 08/06/2022. I tried to calculate this set analysis, with no effect.
sum({<date={'$(=Date(date-7))'}>}sales).
I am sending sample data
data | sales | dynamics |
2022-06-01 | 5 096 307 756 | |
2022-06-02 | 5 091 108 345 | |
2022-06-03 | 5 032 987 096 | |
2022-06-04 | 5 016 091 934 | |
2022-06-05 | 5 004 687 256 | |
2022-06-06 | 5 001 811 683 | |
2022-06-07 | 4 981 518 137 | |
2022-06-08 | 4 975 629 281 | - 120 678 475 |
2022-06-09 | 4 966 636 294 | - 124 472 051 |
2022-06-10 | 4 952 682 149 | - 80 304 947 |
2022-06-11 | 4 943 070 591 | - 73 021 343 |
2022-06-12 | 4 920 126 014 | - 84 561 242 |
2022-06-13 | 4 905 132 083 | - 96 679 600 |
2022-06-14 | 4 889 013 821 | - 92 504 316 |
2022-06-15 | 4 880 600 902 | - 95 028 379 |
2022-06-16 | 4 868 969 430 | - 97 666 864 |
2022-06-17 | 4 830 476 551 | - 122 205 598 |
2022-06-18 | 4 808 291 155 | - 134 779 436 |
2022-06-19 | 4 786 406 608 | - 133 719 406 |
does anyone have any idea how this can be done?
thank you in advance for your help
Hi, do you means the figure in 2022-06-08 dynamic column should be 5,096,307,756?
If yes, you may try with Above function.
unfortunately, this solution is not an option, because user the user can sort the dates. I care about such a pattern: (n)-(n-7), which is the most recent date - the date from 7 days ago. I would like to show such a change on the chart
Then, I would suggest to have some lines in the script. The (n)-(n-7) will always be there no matter how the user sort or select. This will be easier to apply on chart.
tempData:
Load * Inline
[Date, Sales
01.06.2022, 5096307756
02.06.2022, 5091108345
03.06.2022, 5032987096
04.06.2022, 5016091934
05.06.2022, 5004687256
06.06.2022, 5001811683
07.06.2022, 4981518137
08.06.2022, 4975629281
09.06.2022, 4966636294
10.06.2022, 4952682149
11.06.2022, 4943070591
12.06.2022, 4920126014
13.06.2022, 4905132083
14.06.2022, 4889013821
15.06.2022, 4880600902
16.06.2022, 4868969430
17.06.2022, 4830476551
18.06.2022, 4808291155
19.06.2022, 4786406608 ];
FinalData:
Load
Date,
Sales,
Peek(Sales,-7),
Sales - Peek(Sales,-7)
Resident tempData
Order By Date asc;
Drop Table tempData;