3 Replies Latest reply: Oct 14, 2015 7:14 AM by Evgeniy Dobrutskiy

# How to calculate average for last n-days, without "NULL" days?

Hi all, here's my question. I have some number of waiters working with different schedule during month. I'm trying to make a time slice for comparing their achievements during the time. I want to receive slices like - last 7, 10, 14, 30 days. And comparison of all the waiters i have. In these slices I need to use average measures. So my actual question is how to make this slice as in for eg in last 7 days one can have 4 working days and another just one. How can I use only days when they were working (not tacking into account null days)

• ###### Re: How to calculate average for last n-days, without "NULL" days?

Without knowing your data model, the best anyone can do is guess. If the so-called null days are actually null in your data , then you can calculate the averages without concern as nulls are never considered when calculating an average. If these days have 0 values, then its more complex. If you have a working day attribute, you could use a set expression like Avg({<WorkingDay = {1}>} ....).

If you want more specific help, I suggest that you upload your qvw, or a suitable sample.

• ###### Re: How to calculate average for last n-days, without "NULL" days?

Is it ok if I upload here screenshot of my data model as well as load script?

I'm using master calendar, thats why I'm curious.

Scripts:

id,

cash_shift_id,

cash_table_id,

person_id,

dish_name1,

nomenclature1_id,

dish_name2,

nomenclature2_id,

salescript_group_name,

salescript_group_id,

quantity,

points,

created_at,

Date(Floor(created_at)) as Date_created;

SQL SELECT

id,

cash_shift_id,

cash_table_id,

person_id,

dish_name1,

nomenclature1_id,

dish_name2,

nomenclature2_id,

salescript_group_name,

salescript_group_id,

quantity,

points,

created_at

FROM salescript_results_2015;

Cash_positions:

cash_pos_id,

cash_table_id,

cash_order_id,

dish_name,

restaurant_id,

reciept_quantity,

Time(Floor(Frac(reciept_time),1/24/60),'hh:mm') as Time;

SQL SELECT

id AS cash_pos_id,

cash_table_id,

cash_order_id,

dish_name,

restaurant_id,

quantity AS reciept_quantity,

created_at AS reciept_time

FROM cash_positions_2015;

Persons:

person_id,

last_first_name;

SQL SELECT

id AS person_id,

last_name || ' ' || first_name AS last_first_name

FROM persons;

Cash_tables:

cash_table_id,

guest_qt;

SQL SELECT

id AS cash_table_id,

guest_qt

FROM cash_tables_2015;

Restaurants:

restaurant_id,

rest_name;

SQL Select

id AS restaurant_id,

name AS rest_name

FROM restaurants;

MASTER CALENDAR:

QuartersMap:

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

min(Date_created) as minDate,

max(Date_created) as maxDate

Resident Scripts;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

\$(varMinDate) + Iterno()-1 As Num,

Date(\$(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate);

MasterCalendar:

TempDate AS Date_created,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

• ###### Re: How to calculate average for last n-days, without "NULL" days?

P.S. the main parameter is "points"