Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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