0 Replies Latest reply: Jun 26, 2012 1:53 PM by Amitesh Modi RSS

    30 day rolling average

      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.