30 day rolling average

Tue, 26 Jun 2012 17:43:17 GMT

Hi All,

I am having an issue which has two parts and I'm unable to properly form the expressions for it.

I'm trying to calculate 30 day rolling average for a count of help desk tickets something like avg(count(Case ID)).

I have a date field called AsOfDate, which is the snapshot of every ticket taken daily.

In my script however I filter out and bring only the latest AsOfDate.

For the dimensions I use

=if(OpenDate >= (Date('$(vAsofDate)') -30) and OpenDate < Date('$(vAsofDate)'),Date(OpenDate,'MM/DD/YY'))

where vAsofDate returns the latest date and OpenDate returns the last 100 dates

so my questions are:

a.) What should be my Expression to show the 30 day average( or Rolling average which is the average number of opens tickets for the last 30 days) as a single aggregated value and not 30 values. I tried using rangeavg(above(count([Case ID]),0,30)), but it did not work in this case.

It will look something like the following:

30Day Rolling Avg

# of Open Cases 250

which shows an average of 250 open tickets on a daily basis

b.) Could the Dimension expression be the issue?

I would highly appreciate any help.

Thanks,

topic 30 day rolling average in QlikView

30 day rolling average