dynamics sales up to 7 days ago

[Isabelaa_123]

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

[Suxel]

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.

[Isabelaa_123]

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

[Suxel]

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;