topic Calendar Issue in QlikView

Calendar Issue

sqlpuzzle — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calendar Issue

prieper — Wed, 26 Sep 2018 14:32:25 GMT

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

Re: Calendar Issue

sqlpuzzle — Wed, 26 Sep 2018 15:27:41 GMT

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.

Re: Calendar Issue

prieper — Thu, 27 Sep 2018 08:29:21 GMT

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