Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Determining fiscal quarter of a given date (two input files)

ISSUE:

how to determine which fiscal year and quarter a date falls within based on two input files

SCENARIO:

I have two input (CSV) files:

1) timecards

2) fiscal periods

The 1st (timecard) file has a Week ID column that I covert to a date in the data load

(i.e., Date(Date#([Week Id],'DD-MMM-YY'),'MM/DD/YYYY') AS [Week Start],)

The second file, contains the following columns

period type (e.g., "1st Quarter 2014")

period start date (e.g., 02-01-2014

period end date (e.g., 05-30-2014)

I need to determine which fiscal year and fiscal quarter a time card Week ID falls within.

Example

If the Week ID from the timecard file = 03-04-2014

Then the fiscal period (based on input file 2) is

Fiscal year = 2014

Fiscal year-quarter = 1st Quarter 2014

QUESTION

I'd prefer to determine the fiscal periods during the data load.

How can I do this?

NOTE: remember that I transform the string Week ID into a date during the data load also.

Thanks in advance!

4 Replies
Not applicable
Author

Anyone? I assume this kind of lookup is pretty straight forward, but can't find a similar case posted in the community

Anonymous
Not applicable
Author

1. First you need to take the resident of the table you are populating so that you can use the 'Week Start'.

2. Then you can use Year([Week Start]) and QuarterStart([Week Start]) to get what you need.
3. To get the exact format of quarter date use this:

=Ceil(month(Quarterstart([Week Start]))/3)           // Gives the Quarter no.

& if(mod(Ceil(month(Quarterstart([Week Start]))/3),10)=1,'st ',

if(mod(Ceil(month(Quarterstart([Week Start]))/3),10)=2,'nd ', if(mod(Ceil(month(Quarterstart([Week Start]))/3),10)=3,'rd ','th ')))             // Gives you 1st, 2nd, 3rd or 4th

& 'Quarter '

& Year([Week Start])

If you think I didn't understand the problem correctly then provide a sample qvw with code on which I can work upon.

Not applicable
Author

Thanks for replying! I think your answer is CLOSE. I will upload a sample app.

Anonymous
Not applicable
Author

Alright.