topic Re: Master calender for Qlikview app in QlikView

Master calender for Qlikview app

Wed, 06 Jan 2016 08:10:07 GMT

Hi,

I have below date fields in data model.

Order Date,Ship Date,Invoice_date,Statement Date,Credit_date,Purchase Date

I want to create a master calender for my current QV app.

Can anybody please help me on how to decide on which date field should i create this master calender?

Also please let me know if there is any criteria while selecting date field for calender.

Re: Master calender for Qlikview app

sunny_talwar — Wed, 06 Jan 2016 12:54:52 GMT

May be check this link out: Canonical Date

Re: Master calender for Qlikview app

HirisH_V7 — Wed, 06 Jan 2016 12:59:55 GMT

Hi Ankit,

Its got to be decided by your development need ,

Check this,

Linking to two or more dates

-Hirish

Re: Master calender for Qlikview app

Anonymous — Wed, 06 Jan 2016 13:02:41 GMT

A similar kind of thread which explain like how to play with multiple date, is already shared by Sunny T

One of the best explanation for multiple dates master calender...

Re: Master calender for Qlikview app

Thu, 07 Jan 2016 03:40:27 GMT

Thank You!

Re: Master calender for Qlikview app

jagan — Thu, 07 Jan 2016 04:23:11 GMT

Hi,

Check Canonical Date by Henric. If you just want one field as Calendar date then check this with your users/clients and based on that generate the Master calendar using the link below.

Master Calendar Generation Script

Regards,

Jagan.

Re: Master calender for Qlikview app

rajeshforqlikvi — Thu, 07 Jan 2016 05:36:58 GMT

Caltemp:

LOAD

num(min(your required date field)) AS MinDate,

num(max(your required date field)) AS MaxDate

RESIDENT (Use your Transaction Table name from where your date field is coming) ;

LET vMinDate = peek('MinDate', 0, 'Caltemp');

LET vMaxDate = peek('MaxDate', 0, 'Caltemp');

LET vStartDate = peek('MinDate', 0, 'Caltemp');

DROP TABLE Caltemp;

DateIsland:

LOAD

date($(vMinDate) + rowno() - 1) AS D,

year($(vMinDate) + rowno() - 1) AS Y,

month($(vMinDate) + rowno() - 1) AS M,

date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY,

week($(vMinDate) + rowno() - 1) & '-' & year($(vMinDate) + rowno() - 1) AS WY,

// Create Financial year

YearName($(vMinDate)+ rowno()- 1,0,4) as FullFinYear, // Gives output as 2005-2006,2006-2007

date(yearstart($(vMinDate)+ rowno()- 1,0,4),'YYYY') as YearFin, // Gives output as 2005,2006

date((monthsstart(1,date($(vMinDate) + rowno() - 1),0,4)),'MM') & '/01/' & date(yearstart($(vMinDate)+ rowno()- 1,0,4),'YYYY') As FinMonYear

AUTOGENERATE

vMaxDate - vMinDate + 1;

Calendar:

LOAD

D AS DATE,

D AS %TRANDATE,

//Basic Date Dimensions

M AS Month,

if(month(D)<4,floor(month(D)+9),floor(month(D)-3)) as MonthOrder,

Year(D)&Right(100+Month(D),2) As YearMonth,

week(D) AS Week,

weekday(D + 2) AS Weekday,

WY AS WeekYear,

day(D) AS Day,

date(D, 'MM/DD') AS DateMMDD,

Y as Year,

// FINANCIAL Date Dimensions

date(FinMonYear, 'MMM-YYYY') AS FinMonthYear,

YearFin As FinancialYear, // Financial year as 2006,2007

FullFinYear As FullFinYear, // FinancialYear as 2006-2007,2008-2009

'Q' & if(month(D)<4,4,floor(month(D)/3.1)) as FinQuarter,

if(month(D)<4,4,floor(month(D)/3.1)) as NumQuarter,

dual('Q' & if(month(D)<4,4,floor(month(D)/3.1)) & ' ' & YearFin,quarterstart(D)) as FinQuarterYear

RESIDENT

DateIsland;

Drop Table DateIsland;