How to do moving averages over sparse timeseries data

[bonotelscot]

Hello!

I have some sparse timeseries data where each row represents an event at a particular timestamp.  In addition to the raw timestamp, there is also an "Hour" column which is the timestamp truncated to the hour.

I'm trying to figure out an expression that will give me the number of events in the last X hours (24, for example).

A simple accumulation (or above()) won't work as the data is sparse and hours often have no data, especially with more specific selections.  Here is an example across the Hour dimension (continuous) showing the individual events counted by hour (blue bars) and then the same expression accumulating the previous 24 values (red line).  Clearly this isn't correct as the moving average does not decrease over the blank sections of the X-axis.

It feels like there should be an easy solution for this, but I've failed to come up with one.  Options that I've explored are:

1. Set Analysis - couldn't figure this one out, not sure if it's possible

2. Making the data non-sparse - possible, but would result in a ton of empty rows as I'd have to insert all combinations of the various dimensions.

3. Playing around with the chart settings (Suppress Missing, Zero on Bars, etc.) - couldn't get anything to work.  Even if I could this seems like a hack vs. doing it in the expression itself.

I've attached this sample qvw as well as the data used to create it.

Thank you in advance for any help!

[MarcoWedel]

Hi,

one solution might be:

table1:
LOAD RecNo() as EventID,
     Timestamp, 
     Hour, 
     Dim1, 
     Dim2, 
     Dim3, 
     Dim4, 
     Revenue
FROM [https://community.qlik.com/servlet/JiveServlet/download/1562410-341710/sample_data.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

tabAsOfHour:
LOAD Distinct
     Hour,
     Timestamp#(Timestamp(Hour+(IterNo()-1)*'01:00:00','MM/DD/YYYY hh:mm:ss'),'MM/DD/YYYY hh:mm:ss') as AsOfHour,
     IterNo() as IntervalHours
Resident table1
While IterNo()<=24;

see also:

The As-Of Table

hope this helps

regards

Marco