using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

Anonymous — Fri, 29 Apr 2016 01:52:13 GMT

Hi,

I have watched a few youtubes on this but still cant get it to work. Any help appreciated. I need to use the 3 dates in this table in separate Charts. At present the when I use the ”,SurveyCompDateKey,JobCompDateKey dates, the chart still uses the UserDateKey. Any help appreciated.

I have created the following table:

QUALIFY*;
UNQUALIFY "%UserDateKey”,%SurveyCompDateKey",”%JobCompDateKey”;
Summary:
LOAD
Name,

Item,
SurveyCompDateKey as %SurveyCompDateKey,

JobCompDateKey as %JobCompDateKey,
UserCreated as %UserDateKey
;
UNQUALIFY*;

SQL SELECT

Name,

Item,
convert(varchar,CareerQuizCompletedDate,103) as SurveyCompDateKey,
convert(varchar,JobFitDiagnosticCompletedDate,103) as JobCompDateKey ,
convert(varchar,UserCreated,103) as UserCreated,
FROM "Summary".dbo."SummaryUser"

Then I have created the following Calendars (I've only loaded two for this example:-

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min(%UserDateKey) as minDate,
max(%UserDateKey) as maxDate
Resident UserSummary;

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

UserSummaryrCalendar:
Load
TempDate AS %UserDateKey,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as YearMonth,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

//.....................................................Survey date…………………………………..

Temp:
Load
min(%SurveyCompDateKey) as minDate,
max(%SurveyCompDateKey) as maxDate
Resident UserSummary;

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

UserSummaryrCompQuizzCalendar:
Load
TempDate AS %SurveyCompDateKey,
Week(TempDate) As SurveyCompWeek,
Year(TempDate) As SurveyCompYear,
Month(TempDate) As SurveyCompMonth,
Day(TempDate) As SurveyCompDay,
ApplyMap('QuartersMap', month(TempDate), Null()) as QuizCompQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as QuizCompWeekYear,
Date(Date#(Year(TempDate) & Month(TempDate), 'yyyyMMM'), 'MMMyyyy') as QuizCompYearMonth,
WeekDay(TempDate) as QuizCompWeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Re: using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

jolivares — Fri, 29 Apr 2016 02:40:00 GMT

Try to make a schema like this:

Re: using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

marcus_sommer — Fri, 29 Apr 2016 07:13:26 GMT

Maybe this is helpful:

Canonical Date

Why You sometimes should Load a Master Table several times‌

- Marcus

topic using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew in QlikView

using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

Re: using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew

Re: using 3 dates in a table for 3 separate YearMonth Calendars.Qlikivew