3 Replies Latest reply: Jun 19, 2018 8:48 AM by Del Booth RSS

    Date question

    Del Booth



      I am using the below to attempt to sum up values in the field quantity advised for the last week ie Monday 11th to Sunday 17th (week 24).  The problem is if I compare the value from the expression below and then sum the fields in excel for the same week (24) they are not the same.  Is the expression below modified when it is used ie Running it yesterday would give different result to today .


      Sum({<ColYear={'2018'},ColWeek = {"$(=Week(Today())-1)"}>}[Quantity Advised])




        • Re: Date question
          Sunny Talwar

          This could be because of any number of reasons... would you be able to share the Excel file and the app you are using for the comparison?

          • Re: Date question
            Prashant Sangle

            share your app??



            • Re: Date question
              Del Booth



              Unfortunately I am using the desktop freed version.  I want o be able to run the KPI visualization anytime this week and get the quantity advised for the preceding full week. ie run the app today and get data for  11th -17th, run it tomorrow and still get data for the 11th to 17th.I did use Josh Goods approach to generating the master calendar to produce my dates below:



              MAPPING LOAD  

              rowno() as Month, 

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

              AUTOGENERATE (12); 




                             min(Date_Collected) as minDate, 

                             max(Date_Collected) as maxDate 

              Resident [All Orders]; 


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

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

              DROP Table Temp; 




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

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

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




                             TempDate AS Date_Collected, 

                             week(TempDate) As ColWeek, 

                             Year(TempDate) As ColYear, 

                             Month(TempDate) As ColMonth, 

                             Day(TempDate) As ColDay, 

                             YeartoDate(TempDate)*-1 as ColCurYTDFlag, 

                             YeartoDate(TempDate,-1)*-1 as ColLastYTDFlag, 

                             inyear(TempDate, Monthstart($(varMaxDate)),-1) as ColRC12, 

                             date(monthstart(TempDate), 'MMM-YYYY') as ColMonthYear, 

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

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

                             WeekDay(TempDate) as ColWeekDay 

              Resident TempCalendar 

              Order By TempDate ASC; 

              Drop Table TempCalendar;