Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to build a calendar to aggregate by month.
It has a problem with the dates. The date in the Data and Calendar is not linking.
Can some one take a look.
I know it is something real simple. But for an occasional Qlik User like me it is hard to figure out.
think that the v_sell_date in data is a string,
should be converted into a date, like
LOAD
DATE(DATE#(v_sell_date, 'YYYY-MM-DD')) AS v_sell_date,
....
HTH
Peter
Converted the filed to Date as suggested. But the dates got joined but not all date values matched up.
I used this script from ROB WUNDERLICH
MasterCalendar:
Load
TempDate AS OrderDate,
Month(TempDate)& Year(TempDate) as 'Month Year',
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('OrderDate', recno()))-1 as mindate,
max(FieldValue('OrderDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('OrderDate');
This worked for me.
Hi,
seems that there is a mix of number and text in your field v_sell_date.
Either you should clean your datasource or use ALT-funtionality:
LOAD
DATE(ALT(DATE#(v_sell_date), DATE#(v_sell_date, 'YYYY-MM-DD'))) AS v_sell_date,
...
HTH
Peter