Last 3 days average

[mgdotcom]

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

[Chanty4u]

you can try this

Average last three days:

sum({<Date = {'$(=date(today(0)-3))'}>}Measure)/3

Three day rolling average:

rangeavg(above(sum(Measure),0,3))

[YoussefBelloum]

Hi chanty,

here on your first expression, there is not an '>=' missing ?

[Chanty4u]

sorry

Try  in this way

=avg({<Date=,End={'>$(=max(Date)-2)'}>}Measure)

[mgdotcom]

Thank you for your response

sum({<Date = {'$(=date(today(0)-3))'}>}Measure)/3

gives a '0'

rangeavg(above(sum(Measure),0,3))

gives me the same value as the current day

[mgdotcom]

Thanks again.

I'm not sure about the syntax in =avg({<Date=,End={'>$(=max(Date)-2)'}>}Measure)

[Chanty4u]

here end is Enddate.