topic Re: Last 3 days average in QlikView

Last 3 days average

mgdotcom — Wed, 28 Feb 2018 13:17:43 GMT

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

Chanty4u — Wed, 28 Feb 2018 13:21:25 GMT

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

YoussefBelloum — Wed, 28 Feb 2018 13:33:59 GMT

Hi chanty,

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

Re: Last 3 days average

Chanty4u — Wed, 28 Feb 2018 13:37:51 GMT

sorry

Try in this way

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

Re: Last 3 days average

mgdotcom — Wed, 28 Feb 2018 13:46:35 GMT

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

Re: Last 3 days average

mgdotcom — Wed, 28 Feb 2018 13:48:29 GMT

Thanks again.

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

Re: Last 3 days average

Chanty4u — Wed, 28 Feb 2018 13:50:22 GMT

here end is Enddate.