Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

delbooth
New Contributor II

Date question

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

3 Replies
MVP
MVP

Re: Date question

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

share your app??

Regards,

Regards,
Prashant Sangle
delbooth
New Contributor II

Re: Date question

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;