Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
=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.
Thanks for replying! I think your answer is CLOSE. I will upload a sample app.
Alright.