7 Replies Latest reply: Jul 7, 2017 7:53 AM by Jason Campbell RSS

    Count occurrences of selected day records across the prior xxx #days

    Jason Campbell

      I am tasked with creating a QV app that I’m not sure how to approach and hoping someone can give me some guidance.

       

      It initially sounded simple, but after some time spinning my wheels I found that it is not (at least for me).  The app needs to look at distinct telephone numbers for the previous day (only), then look back x number of days (x=variable set by date selected) to see if they called on each day throughout the date range (Range = Date selected thru Previous day).  If they haven’t called on any of the previous days, then it’s a Yes for that telephone number.  If they have called, it’s a No.   # of Yes / # of No = First Call Resolution %.

       

      I started out using this methodology/approach:

      NoConcatenate
      PrevDay:
      Load Distinct
      datekey,
      cName,
      TelephoneNum,
      TelephoneNum as pdTelephoneNum,
      1
      as pdCT
      Resident Calls
      Where datekey = Date(Today(1)-1);

      NoConcatenate
      PrevMth:
      Load Distinct
      TelephoneNum,
      TelephoneNum as pmTelephoneNum,
      If(datekey <= Today(1)-2 and datekey >= Today(1)-8,1) as Prev7CT
      Resident Calls
      Where Exists (pdTelephoneNum,TelephoneNum)
      and  datekey <= Today(1)-2 and datekey >= Today(1)-8;

       

      Expression:

      (RangeSum(Above(Sum({<datekey>}Prev7CT), 0, 6)) * Avg(1)) / (RangeSum(Above(Sum({<datekey>}pdCT), 0, 1))* Avg(1))

       

      It appears to work fine, but is limited to the date ranges.  I might ultimately have to do this in seven-day blocks to view trending.

       

      Is there a way to use an expression to do this using the raw data (not grouped in the load script)?  Something like Count(datekey={">=$( vSelectedStop) <=$( vSelectedStart)"} TelephoneNum) /  Count(datekey={‘$(vSelectedStart)’}>}TelephoneNum.  I can’t get it to work, which I’m thinking is due to (non) aggregation.  I’m spinning my wheels…

       

      Many thanks in advance.