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

# Last 3 days average

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

• ###### Re: Last 3 days average

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))

• ###### Re: Last 3 days average

Hi chanty,

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

• ###### Re: Last 3 days average

sorry

Try  in this way

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

• ###### Re: Last 3 days average

Thanks again.

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

• ###### Re: Last 3 days average

here end is Enddate.

• ###### Re: Last 3 days average

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