Question

Financial Year Calc

  • 26 October 2017
  • 3 replies
  • 11 views

Badge

(not sure how to cast that _year attribute to an integer)

Hi,

I need to only filter records from a date field that are in the current financial year.

e.g.

if month(current_date) => July

start_date = 1st July getYear()

end_date = 30 June getYear(+1)

else

start_date = 1st July getYear() -1

end_date = 30 June getYear()


3 replies

Userlevel 4
Badge +25

I would suggest the DateTimeCalculator transformer.

  • Use the mode "Calculate Interval between Datetimes"
  • Set the Start Datetime = Record Date Attribute
  • Set the End Datetime = 30th June (actually 20170630)
  • Set Result Type = Days

If the result is positive, then it's the current year (ie past 30th June) if it's negative then it's the previous year (before 30th June).

Hope this helps. Also wanted to check about the VariableSetter/Retriever transformers. Not sure you really need those. You could just use an AttributeManager to do the same thing. Currently you set a variable and bring it straight back into an attribute, which isn't necessary.

Regards

Mark

Userlevel 4
Badge +25
I posted an answer below with what I think is the best solution. But if you want to try and debug your current workspace... I don't think you should need to cast the result of your expression to an integer. What I would do is make sure that the expression is actually producing the value you want. So, take your expression from the TestFilter - the @DateTimeFormat(@Value(op_e_date),%m) part - and put it into an AttributeManager to actually create that value. Then add a Logger/Inspector and run the workspace to see what the result is. If it looks correct, then test that attribute to see if it equals 7. Then at least we'll know whether the issue is in the expression or the testing of it.

 

Userlevel 2
Badge +17

If a financial year begins at July 1st every year, the financial year of a given date is equal to the calendar year of the day before 6 months. So I would compare the values returned by these two expressions..

@DateTimeFormat(@DateTimeAdd(@DateTimeNow(),-P6M),%Y)
@DateTimeFormat(@DateTimeAdd(@Value(op_e_date),-P6M),%Y)

See here to learn more about the powerful FME Date/Time Functions: Date/Time Functions

Reply