Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

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

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

utkarshgarg
Contributor II

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

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

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

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

utkarshgarg
Contributor II

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

Alright.