Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You can try this.

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

From xyz.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Thanks Sunny.

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

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

No problem at all

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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,

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try this.

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


Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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