Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am new to QS and am trying to prepare my data so that I can create visualizations.
In Excel I would use formula to convert a financial year to a date so that I can calculate between dates. For example the source data would show "FY 2015/2016" and I want to add a column in the data to convert it into "30/06/2016" as a date (then I can calculate age using DOB). Is there any function in QS that I can use to do it? Or if it's more convenient to do a "master dimension" I'll also be interested to know how.
Thanks very much in advance for any help!
Hi,
You can try this.
Load *,Makedate(30,06,Right(Year,4)) as Date
From xyz.
Regards,
Kaushik Solanki
I think you messed up the order bro, may be you meant this
Load *,Makedate(Right(Year,4), 6, 30) as Date
From xyz.
you can also try this:
Date(MakeDate(SubField([Financial Year], '/', -1), 6, 30)) as Date
Thanks Sunny.
You are right I messed up the order... Thanks for correcting.
Regards,
Kaushik Solanki
No problem at all
Many thanks Sunny and Kaushik for your replies. The MakeDate function worked beautifully.
Now how do I convert back? Eg. from 30/06/2016 to FY 2015/2016? I have tried below but it doesn't seem to work...
"FY " & right(RefDate,4) - 1 & "/" & right(RefDate,4) as ReportFY
Hi all,
I have actually worked it out myself...This works but if you have a better idea I will be very interested to hear!
'FY ' & (year([RefDate]) - 1) & '/' & year([RefDate]) as ReportFY,
Hi,
Try this.
"FY " & Year(RefDate) - 1 & "/" & Year(RefDate,) as ReportFY
Regards,
Kaushik Solanki
A better way would be to create a calendar that incorporates financial periods as well as calendar dates. If you search here for master calendar and fiscal/financial period you will find a wealth of posts that will get you going.
Thanks Kaushik. For some reason the double quote didn't work but single quote worked...I'm always confused as to where to use double quote, single quote and square brackets...