Considering 2022-06 as starting month,
If month is 2022-07, i should assign 2022-06's corresponding field values " greater_6_mon" to 2022-07's field "prev" , likewise to 2022-08 as well
Here are my values :
month prev greater_6_mon
2022-06 26
2022-07 2
2022-08 1
expected result: (please suggest)
month prev greater_6_mon
2022-06 0 26
2022-07 26 2
2022-08 2 1
Your initial post didn't seem to have enough information, as you also want to do that by 'Team', in which case you will need to use streamstats, as @richgalloway states, however, you will need to modify it slightly to take account of the team split, i.e.
| streamstats window=1 current=f global=f first(greater_6_mon) as prev by team
| fillnull value=0 prev
Simplest option is
| autoregress greater_6_mon as prev
Your initial post didn't seem to have enough information, as you also want to do that by 'Team', in which case you will need to use streamstats, as @richgalloway states, however, you will need to modify it slightly to take account of the team split, i.e.
| streamstats window=1 current=f global=f first(greater_6_mon) as prev by team
| fillnull value=0 prev
Thank you so much @bowesmana and @richgalloway . This worked perfectly as intended. Great learning!!
This worked fine but it is taking preceding value but I am expecting previous values of previous month.
Example :
My values :
team | month | prev | greater_6_mon |
A | 2022-07 | 0 | 26 |
B | 2022-07 | 0 | 2 |
C | 2022-07 | 0 | 1 |
D | 2022-07 | 0 | 8 |
Expected :
team | month | prev | greater_6_mon |
A | 2022-08 | 26 | 16 |
B | 2022-08 | 2 | 22 |
C | 2022-08 | 1 | 21 |
D | 2022-08 | 8 | 18 |
Here my greater_6mon of 2022-07 has been assigned to prev of 2022-08. Thats my intention to achieve.
Please suggest
Here's an untested idea
| streamstats window=2 first(greater_6_mon) as prev
| fillnull value=0 prev
The streamstats command computes stats on results as they're generated. The window=2 option says to look only at the current event and the one preceding it. The first function takes the greater_6_mon value from the previous event and stores it in the prev field of the current event.
Thank you soo much, worked according to my initial requirement.