Financial Year calculation?

[#1]

Our financial year goes from July 2012 to Jun 2013, so when someone captures a record, they'll put it for the financial year, which is 2013.

In my QV I need to now load the data for the current financial year, but I can't use something like "where Financial_Year = year(now()) + 1" because as soon as it's December moving over to January, the report will start looking for 2014 where it's still supposed to look for 2013.

I'm sharing a QVD with multiple reports (other people report off of the same data), so I put the where statement on the load thereof in the script section.

Is there any way I can format the Where clause to get the current financial year automatically?

[swuehl · Accepted solution]

Try

...

where Financial_Year = year(yearstart(today(1),1,7));

[#3]

Hi There,

Can I suggest using a lookup table in SQL, something like DayDate, FinancialYear, then in your SQL Statement, do an

INNER JOIN tblDateLookup AS DL

ON DL.DayDate = YOURTABLE.TransactionDate

Then you can do a WHERE statement

WHERE DL.FinancialYear = '2014'

That should do the trick. Even if you don't want to do it in SQL, do it as an inline table, as a hint, generate your dates and financial years by dragging columns down in excel. Hope this all makes sense,

Kind Regards,

Miles

[#4]

That would rely on someone manually updating the lookup table and/or excel spreadsheet, plus I'm working off of an already QVD file that gets data sources from more than 1 place (sql/excel etc) and transforms it into an equal playing field (because some people capture financial year 2012/13 and other 13 or even 2013)

Thanks for the suggestion though. I'll try swuehl's method, but I'm not sure how to test it other than waiting 4 months

[swuehl]

Thanks for the suggestion though. I'll try swuehl's method, but I'm not sure how to test it other than waiting 4 months

replace the today(1) with makedate(2013,1,1) or any other date...