Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jadewind
Contributor

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!

10 Replies

Re: Function to convert financial year to date

Hi,

You can try this.

Load *,Makedate(30,06,Right(Year,4)) as Date

From xyz.

Regards,

Kaushik Solanki

Re: Function to convert financial year to date

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

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

jadewind
Contributor

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

jadewind
Contributor

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

Hi,

Try this.

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


Regards,

Kaushik Solanki

MVP
MVP

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.

jadewind
Contributor

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

Community Browser