Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
dineshm030
Creator II
Creator II

Create the Master Calendar With 5-4-4 Weeks

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 MonthEnd Date for the MonthFinancial YearQuarter#Month#
28-09-202001-11-2020FY2020-2111
02-11-202029-11-2020FY2020-2112
30-11-202027-12-2020FY2020-2113
28-12-202031-01-2021FY2020-2124
01-02-202128-02-2021FY2020-2125
01-03-202128-03-2021FY2020-2126
29-03-202102-05-2021FY2020-2137
03-05-202130-05-2021FY2020-2138
31-05-202127-06-2021FY2020-2139
28-06-202101-08-2021FY2020-21410
02-08-202129-08-2021FY2020-21411
30-08-202126-09-2021FY2020-21412

 

Kindly help me to create this.

Thanks in advance.

Regards,

Dinesh Kumar Mani.

2 Replies
dineshm030
Creator II
Creator II
Author

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
MVP & Luminary
MVP & Luminary

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