Date question

[delbooth]

Hi.

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])

Thanks

Del

[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?

[PrashantSangle]

share your app??

Regards,

[delbooth]

Thanks.

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:

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

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

AUTOGENERATE (12); 

Temp: 

Load 

               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; 

TempCalendar: 

LOAD 

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

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

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

CollectCalendar: 

Load 

               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;