10 Replies Latest reply: Aug 2, 2016 9:07 PM by jade wind

Function to convert financial year to date

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!

• Re: Function to convert financial year to date

Hi,

You can try this.

From xyz.

Regards,

Kaushik Solanki

• Re: Function to convert financial year to date

Kaushik

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

• Re: Function to convert financial year to date

Thanks Sunny.

You are right I messed up the order... Thanks for correcting.

Regards,

Kaushik Solanki

• Re: Function to convert financial year to date

No problem at all

• Re: Function to convert financial year to date

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

• Re: Function to convert financial year to date

Hi,

Try this.

"FY " & Year(RefDate) - 1 & "/" & Year(RefDate,) as ReportFY

Regards,

Kaushik Solanki

• Re: Function to convert financial year to date

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...

• Re: Function to convert financial year to date

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,

• Re: Function to convert financial year to date

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.

• Re: Function to convert financial year to date

Thanks Jonathan. I will check it out...