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

    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)

       

      Thank you all in advance!

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

          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.

               

               

              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;  screenshot 4.png