How to aggregate a count over time

[Anonymous]

Hi folks,

I need an increasing count over time for individual items, and not sure if it can be done in the script or not. The end users will need to be able to select the count be it 1, 2, 3, 4, 5 or more.

Say my data looks like this...(already sorted by item and month)

item, month

12345, 1/1/2022

12345, 3/1/2022

12345, 5/1/2022

54321, 2/1/2022

54321, 4/1/2022

54321, 6/1/2022

54321, 9/1/2022

My goal is to display as:

item	month	count
12345	1/1/2022	1
12345	3/1/2022	2
12345	5/1/2022	3
54321	2/1/2022	1
54321	4/1/2022	2
54321	6/1/2022	3
54321	9/1/2022	4

Any ideas?

Regards

JohnInSD

(aka johncaqc)

[Vegar · Accepted solution]

try this peek approach.

Load item, month, if(peek('item')<>item, 1, peek('itemcount') + 1) as itemcount

From ...

[Anonymous]

Thank you, Vegar, that works beautifully! Now, I just need to apply it to my dataset.

V/r,

John