Discussion board where members can learn more about Qlik Sense App Development and Usage.
how to determine which fiscal year and quarter a date falls within based on two input files
I have two input (CSV) files:
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.
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
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!
Anyone? I assume this kind of lookup is pretty straight forward, but can't find a similar case posted in the community
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:
& if(mod(Ceil(month(Quarterstart([Week Start]))/3),10)=1,'st ',
& '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.
Thanks for replying! I think your answer is CLOSE. I will upload a sample app.