Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Specify Quarter dates

Hi everyone

This is my first post as am a little stuck on a date issue.

I have a scenario where there are two types of clients and quarterly valutions are done as per dates below

Days per quarter:

Quarterly:

Q1 – Ending 31/03/yyyy – 90

Q2 – Ending 30/06/yyyy – 91

Q3 – Ending 30/09/yyyy – 92

Q4 – Ending 31/12/yyyy - 92

Quartertax

Q1 – Ending 05/04/yyyy – 95

Q2 – Ending 30/06/yyyy – 86

Q3 – Ending 30/09/yyyy – 92

Q4 – Ending 31/12/yyyy - 92

I am unable to figure out a function whereby I can specify the dates in each quarter for past years and futureproofing so that I can assign the correct quarter to the dates within that quarter.

Any help would be greatly appreciated.

Many Thanks

Sye

1 Reply
Not applicable

I would recommend creating a date table that includes quarters. Pre-creating your date data will have all kinds of benefits not the least of which include simplified development and better performance. Here's a snippet of code that I would use to pre-prepare quarters:

select

//include all your other date attributes like month, week, day of year, year, etc...

[QuarterID] = convert(INT,convert(varchar(4),datepart(YYYY,getdate()))+convert(varchar(2),datepart(QQ,getdate()))) ,

[Quarter_YYYY-QQ] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_YYYY-Q] = convert(varchar(4),datepart(YYYY,getdate()))+'-'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_QQ-YYYY] = 'Q'+convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())),

[Quarter_Q-YYYY] = convert(varchar(2),datepart(QQ,getdate()))+'-'+convert(varchar(4),datepart(YYYY,getdate())) ,

[Quarter_QQ] = 'Q'+convert(varchar(2),datepart(QQ,getdate())) ,

[Quarter_Q] = convert(int,datepart(QQ,getdate()))

Instead of getDate() you would want to create a script that would loop through days from "startdate" to "finishdate" to pre-build a calendar. However, the above code would work for current day as-is.

On a related note, you may also want to add offsets (current Q is 0, last Q is -1, prior is -2, etc) to your date table so you can easily do QTD and Current vs Previous evaluations.