topic How to aggregate a count over time in QlikView

How to aggregate a count over time

Anonymous — Mon, 24 Oct 2022 23:51:17 GMT

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)

Re: How to aggregate a count over time

Vegar — Tue, 25 Oct 2022 05:49:57 GMT

try this peek approach.

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

From ...

Re: How to aggregate a count over time

Anonymous — Tue, 25 Oct 2022 17:04:07 GMT

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

V/r,

John