Trying to build financial calendar from existing SQL

[Simon_W]

Hello!

I'm trying to build a financial calendar from within Qlik. I already have this in SQL, extracting the time periods from my IBM iSeries. The problem is they are all held in the same file, and separated by a flag for each type (D/W/M/Y). Below is the query that I run, however I'm really struggling trying to figure out how I would replicate this in the Qlik environment. Any help gratefully received! 

select
D.DYR# AS "Financial year",  -- 2018
D.DPER AS "Day of year",  -- 1, 2, 3 etc
D.DBDY AS "Day date",  -- 20171231
digits(d.dyr#)||'-'||digits(d.dper) as "Day name", -- 2018-001
W.DPER as "Week of year",  -- 1
W.DBDY as "Week begin date",  -- 20171231
W.DEDY as "Week end date",  -- 20180106
digits(d.dyr#)||'-'||RIGHT(digits(w.dper),2) as "Week name", --2018-01
P.DPER as "Month of year", -- 1
P.DBDY as "Month begin date",  
P.DEDY as "Month end date",
digits(d.dyr#)||'-'||right(digits(p.dper),2) as "Month name"
FROM IPTSFIL.IPHSCAL D
left outer join IPTSFIL.IPHSCAL W ON W.DBDY <= D.DBDY AND W.DEDY >= D.DEDY AND W.DTYP = 'W'
left outer join IPTSFIL.IPHSCAL P ON P.DBDY <= D.DBDY AND P.DEDY >= D.DEDY AND P.DTYP = 'P'
left outer join IPTSFIL.IPHSCAL S ON S.DBDY <= D.DBDY AND S.DEDY >= D.DEDY AND S.DTYP = 'S'

where D.dtyp = 'D'
AND D.DYR# >= 2018

[Mark_Little]

Hi Simon,

I would normally build my own calendar within the script using the date field i want the app drive bu something like below.

SET vFinYearOS = -3; //Would give an April financial start date. 

YEAR(ADDMONTHS(TempDate,$(vFinYearOS)))&'/'&YEAR(ADDYEARS(ADDMONTHS(TempDate,$(vFinYearOS)),1)) AS FIN_YEAR,
YEAR(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_YEAR_START,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS))))&'-'&YEAR((ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_YEAR,
NUM(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))) AS FIN_MONTH_NO,
DAY(ADDMONTHS(TempDate,$(vFinYearOS))) AS FIN_DAY,
DUAL('Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3), CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3)) AS FIN_QUARTER,
'Q' & CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_DESC,
CEIL(MONTH(ADDMONTHS(TempDate,$(vFinYearOS)))/3) AS FIN_QUARTER_NO,

Hope this helps

Mark

[Simon_W]

Hi Mark,

I will certainly try that - thank you. I think part of the challenge is our year is 5/4/4 - i.e. 5 weeks in Jan, 4 weeks, in Feb and Mar, 5 weeks in April, etc. - then there is the challenge of leap years where we take an extra week (53 weeks every 7 years), which is why I was pulling from a static file.

I'll give this a go though thanks!

Simon.