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,
AM
