6 Replies Latest reply: Feb 28, 2018 8:50 AM by Chanty 4u RSS

    Last 3 days average

    Mervin Govender

      Good day,

       

      I've tried a few different ideas to get an average of leads generated but I don't seem to be able to get it right. I'm either returned a value of 0 or the value of leads for the date I have selected.

       

      It should calculated as an average of the last 3 days excluding weekends.

       

      One

      ______________________________________________________________

      avg({<[Date] = {">=$(vMaxDateLess3) <=$(vMaxDateNoWeekend)"}client_category_target_lead_count)

       

      vMaxDateLess3 =Date(if(weekday(vMaxDateNoWeekend-3)='Fri',vMaxDateNoWeekend-4,

             if(weekday(vMaxDateNoWeekend-3)='Sat',vMaxDateNoWeekend-4,

              if(weekday(vMaxDateNoWeekend-2)='Sat',vMaxDateNoWeekend-3,

              vMaxDateNoWeekend-2))),'YYYY/MM/DD')

       

      vMaxDateNoWeekend=Date(if(weekday(vMaxDate-1)='Sat',vMaxDate-2,

            if(weekday(vMaxDate-1)='Sun',vMaxDate-3,

            if(match(Date(vMaxDate-1,'YYYY-MM-DD'), $(vPHolidays)) and not Match(weekday(vMaxDate-2),'Sat','Sun') ,vMaxDate-2,

            if(match(Date(vMaxDate-1,'YYYY-MM-DD'), $(vPHolidays)) and Match(weekday(vMaxDate-2),'Sun') ,vMaxDate-4,

            if(match(Date(vMaxDate-1,'YYYY-MM-DD'), $(vPHolidays)) and Match(weekday(vMaxDate-2),'Sat') ,vMaxDate-3,  vMaxDate-1))))),'YYYY/MM/DD')

       

      vMaxDate= max(Date)

       

       

      Two

      _________________________________________________________________

      =sum({<DATE={'$(=Date(max(DATE)-3)))'}, Month, Day>} client_category_target_lead_count)/3

       

      Three

      __________________________________________________________________

      =((sum({<Date = {">=$(=Date(today()-3))<=$(=Date(today()))"}>} client_category_target_lead_count))

      +(sum({<Date = {">=$(=Date(today()-2))<=$(=Date(today()))"}>} client_category_target_lead_count))

      +(sum({<Date = {">=$(=Date(today()-1))<=$(=Date(today()))"}>} client_category_target_lead_count)))/3