Comparing YTD Position

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have safety incidents. I would like to compare incidents year on year (this FY versus last year but in a YTD fashion).  So that if my FY is Jul-Jun - I do have a FY field) and I am in July, I would like to know how many incidents were recorded in the same period last FY, i.e. July FY 16 versus Jul FY 17.  I cannot see to find a solution. I did manage to work out in Excel by referring to certain dates.   

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Can you describe your table relationships?  Or is it just a single table of safety incidents?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks.  These incidents do belong to a project so I have one to many relationship.   I do record incident date in the incident table. FY is being passed down from projects.
One way to deal with this is to have each record self identify with a formula checkbox field as to if it is within the FY.  Then another checkbox field to calculate to true of it was within the previous FY.  Then you have a clean hook to do summary fields or to have embedded report of safety incidents.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. Will try this.  I was hoping there may be some VAR option to achieve this as a formula.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
There is one issue. I would like to work out a corresponding period, meaning, months elapsed this year versus last year.  How could that be achieved?
Sorry, but I am not understanding your question.  The # of months elapsed this YTD is the same as the # of months elapsed for the Last Year To Date.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
That's right. But how do I establish this using a formula? As this would change each passing month.
Month(Today())

For example today is July 11, 2016.  Month(Today()) = 7.  The # of months which have passed so far this year is 7.  Or maybe 6 if you only want to count completed months.