Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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;
P.S. the main parameter is "points"