# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor

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

______________________________________________________________

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

_________________________________________________________________

Three

__________________________________________________________________

Tags (2)
6 Replies
Esteemed Contributor III

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

Esteemed Contributor

## Re: Last 3 days average

Hi chanty,

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

Esteemed Contributor III

## Re: Last 3 days average

sorry

Try  in this way

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

New Contributor

## Re: Last 3 days average

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

New Contributor

## Re: Last 3 days average

Thanks again.

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

Esteemed Contributor III

## Re: Last 3 days average

here end is Enddate.