Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
i have got a table (my example):
Period | 1 | 2 | 3 | 4 | 5 |
MyValues | 10 | 15 | 40 | 55 | 70 |
Expected Output: sum of 3 previous periods |
10 | 25 | 65 | 110 | 165 |
my expected output is: how can i calculate the sum of 3 previous periods in script?
i can solve this issue by using this expression on Userinterface as expression:
sum(aggr(rangesum(above(sum(MyValues),0,3)),Period))
but i want to know, how i can transfer this expression in script?
Does anybody have any idea? Thanks a lot Bek
You need an appropriated sorted resident-load and within it something like:
rangesum(Field, peek('Field', -1), peek('Field', -2)) as X
Further you may need some if-loops to check which records are the first/second per your key-field(s) to adjust the measure to just take the current respectively the current and the previous value. And of course if there are multiple values per Field you need to aggregate them in beforehand - again with the right group by dimensions and any filters if needed.
You need an appropriated sorted resident-load and within it something like:
rangesum(Field, peek('Field', -1), peek('Field', -2)) as X
Further you may need some if-loops to check which records are the first/second per your key-field(s) to adjust the measure to just take the current respectively the current and the previous value. And of course if there are multiple values per Field you need to aggregate them in beforehand - again with the right group by dimensions and any filters if needed.
Hello Marcus,
sorry for late responce, thank you very much for your time and help