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

[#1]

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)

Thank you all in advance!

[jonathandienst]

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.

[#3]

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.

LOAD SCRIPT:

Scripts:

LOAD

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:

LOAD

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:

LOAD

person_id,

last_first_name;

SQL SELECT

id AS person_id,

last_name || ' ' || first_name AS last_first_name

FROM persons;

Cash_tables:

LOAD

cash_table_id,

guest_qt;

SQL SELECT

id AS cash_table_id,

guest_qt

FROM cash_tables_2015;

Restaurants:

LOAD

restaurant_id,

rest_name;

SQL Select

id AS restaurant_id,

name AS rest_name

FROM restaurants;

MASTER CALENDAR:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

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

AUTOGENERATE (12); 

Temp: 

Load 

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: 

LOAD 

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

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

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

MasterCalendar: 

Load 

  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; 

[#4]

P.S. the main parameter is "points"