Don't count anything that happened within 30 minutes

[#1]

I have:

ID	Type	Timestamp	Count
1	A	1/1/2013 1:00	1
1	B	1/1/2013 1:25	1
1	C	1/1/2013 1:29	1
1	D	1/1/2013 1:30	1
1	E	1/1/2013 1:59	1
1	F	1/1/2013 2:07	1

I want:

ID	Type	Timestamp	Count
1	A	1/1/2013 1:00	1
1	D	1/1/2013 1:30	1
1	F	1/1/2013 2:07	1

Basically, once a record is entered (1/1/2013 1:00), I don't want to count any additional entries until 30 minutes have passed.  Once 30 minutes have passed, I can accept a new record, and the 30 minute timer starts again.  This is for a straight table, and I would prefer to solve it using set analysis than to do it in the script, but I can if needed.  Any ideas?

[Oleg_Troyansky · Accepted solution]

You can't do it entirely in Set Analysis, because Set Analysis expression gets evaluated globally, once per chart, so it can't be sensitive to the value (timestamp) of the previously displayed row.

You can prepare certain flags in the script, but then your choice of "visible" vs. "invisible" transactions becomes rigid, i.e. not dependent of user selections.

What if, based on the user selections, the second line with Type=B becomes the first "available", then the counter should start from that line and not from the first line.

I can't see a very elegant solution here... If your dataset is very small and you are not concerned about scalability, you could use an IF statement and compare the timestamp from the previous line in the chart (using ABOVE()) to the current timestamp.

For a large datasets, you'll have to make some assumptions and pre-calculate some of those rules in the load script.

[Anonymous]

Hi David,

I tried in the script with date, skipping nine and loading each 10th:

LOAD

date(if(date-peek(date)<10, peek(date), date)) as date,

Amount

FROM ...

So, it should work with time as well.  30 min is 1/48 of a day, so it could be something lkike this (maybe with some adjustments):

if(Timestamp-peek(Timestamp)<(1/48), peek(Timestamp), Timestamp)

Make sure that the data is ordered by Timestamp ascending.

Regards,

Michael