Create the Master Calendar With 5-4-4 Weeks

[dineshm030]

Hi All,

I have to create one master calendar with 5-4-4 weeks the given below: For every quarter of the first month should have 5 weeks based on the start date and end date.

Start Date for the Month	End Date for the Month	Financial Year	Quarter#	Month#
28-09-2020	01-11-2020	FY2020-21	1	1
02-11-2020	29-11-2020	FY2020-21	1	2
30-11-2020	27-12-2020	FY2020-21	1	3
28-12-2020	31-01-2021	FY2020-21	2	4
01-02-2021	28-02-2021	FY2020-21	2	5
01-03-2021	28-03-2021	FY2020-21	2	6
29-03-2021	02-05-2021	FY2020-21	3	7
03-05-2021	30-05-2021	FY2020-21	3	8
31-05-2021	27-06-2021	FY2020-21	3	9
28-06-2021	01-08-2021	FY2020-21	4	10
02-08-2021	29-08-2021	FY2020-21	4	11
30-08-2021	26-09-2021	FY2020-21	4	12

Kindly help me to create this.

Thanks in advance.

Regards,

Dinesh Kumar Mani.

[dineshm030]

How to run the below SQL script in Qlikview. For me it is showing error.

declare @dtFiscalYearStart smalldatetime

, @dtFiscalYearEnd smalldatetime

, @iTemp int

---This is the table we'll populate and return at the end

declare @tb table(DayOfFiscalYear int identity (1,1)

,CalendarDate date

, FiscalWeek int

, FiscalMonth tinyint

, FiscalQuarter tinyint)

--Now, populate our variables:

--This can be any date you choose. We assume that the fiscal year

--begins on the first day of the "fiscal week"

--We explicity populated STart of Fiscal Year and End

set @dtFiscalYearStart = 'September 28, 2020'

set @dtFiscalYearEnd = 'September 26, 2021'

set @iTemp = 0

--Here's the loop to populate our output table:

while not exists(select * from @tb where CalendarDate >= @dtFiscalYearEnd)

begin

insert into @tb (CalendarDate, FiscalWeek)

select dateadd(dd, @iTemp, @dtFiscalYearStart), (@iTemp / 7) + 1

set @iTEmp = @iTemp + 1

end

update @tb set FiscalMonth = 1, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek <= 5

update @tb set FiscalMonth = 2, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek <= 9

update @tb set FiscalMonth = 3, FiscalQuarter = 1 where fiscalMonth is null and fiscalweek <= 13

update @tb set FiscalMonth = 4, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek <= 18

update @tb set FiscalMonth = 5, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek <= 22

update @tb set FiscalMonth = 6, FiscalQuarter = 2 where fiscalMonth is null and fiscalweek <= 26

update @tb set FiscalMonth = 7, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek <= 31

update @tb set FiscalMonth = 8, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek <= 35

update @tb set FiscalMonth = 9, FiscalQuarter = 3 where fiscalMonth is null and fiscalweek <= 39

update @tb set FiscalMonth = 10, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek <= 44

update @tb set FiscalMonth = 11, FiscalQuarter = 4 where fiscalMonth is null and fiscalweek <= 49

update @tb set FiscalMonth = 12, FiscalQuarter = 4 where fiscalweek >= 49

--Be sure you recognize that going 4-4-5, 4-4-5, 4-4-5, 4-4-5 does not a whole year.

--you're still a couple days short. In calendar year 2007, there are three days in the 53rd week!

--uncomment this for a double-check of week counts

---select FiscalMonth,count(distinct fiscalWeek) from @tb group by FiscalMonth

select * from @tb

@sunny_talwar @marcus_sommer 

[marcus_sommer]

If your sql is working like your requirements you didn't need to change it - just putting it into Qlik and letting it run against your database. Qlik itself doesn't execute the sql else just transfers per driver the statement and received later the result. Only the database and the used driver must be capable for the applied features and syntax.

If this isn't the case you should discard the sql and creating the calendar directly with Qlik logic. The following posting provides a lot of links about calendars in general as well as specialized calendars, like 4-4-5 or fiscal calendars:

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

- Marcus