Report on Recurring Dates Annually?

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
I am trying to create a report that shows "Birthdays within the next 7 days" and "Anniversaries This Quarter" and I am hitting a brick wall due to the "year" part of the DOB/Start Date fields. Any suggestions for how I could manipulate a DOB or anniversary date field into being able to report "Birthdays in the Current Week", "Birthdays Next Week", and "Anniversaries in the Current Quarter"?
Photo of Jim

Jim

  • 40 Points

Posted 4 months ago

  • 0
  • 1
Photo of Rob White IV

Rob White IV

  • 948 Points 500 badge 2x thumb
You should be able to do this with a report filter.

Filter by the date field and set it 'on or before' some number of 'days'.  

Let me see if I can get you a screenshot.

~Rob
Photo of Rob White IV

Rob White IV

  • 948 Points 500 badge 2x thumb
Here is an example of a report I have that looks 1 year into the past for items.  You can do something similar for your stuff.



Hope this helps!

~Rob
Photo of Jim

Jim

  • 40 Points
Rob, thanks for the comment. In my case, I am not sure that this would not work, as we are trying to capture the date and month of the DOB and report on it year over year. My initial thought was to have some sort of concatenate date formula by converting the date and month to numeric values and marry those values with the current year, creating a copy of the DOB in the current year and next year. 

Also, I have the quarters setup in QB already to follow a calendar schedule, so as long as I can convert the date to the current year, I should be in good shape. 
Photo of Rob White IV

Rob White IV

  • 948 Points 500 badge 2x thumb
The 'current quarter' report will be a bit more tricky as you will need to teach your QuickBase instance what a quarter is.  But once you do that with a formula field, you can use the formula field in a filter to do the same.

I have an example of doing that with the 'Activity Request Date' field pictured above.  I created a formula field called 'LS - Action Quarter' to calculate the quarter of this particular contract my 'LS - Action' occured.  The formula is this ->

var Text monthNum = ToText(Month([Activity Request Date]));

If($monthNum="10" or $monthNum="11" or $monthNum="12","1st",
If($monthNum="1" or $monthNum="2" or $monthNum="3","2nd",
If($monthNum="4" or $monthNum="5" or $monthNum="6","3rd",
If($monthNum="7" or $monthNum="8" or $monthNum="9","4th",
If($monthNum="" or $monthNum=null,"No Activity Request Date Set for this Action.",
"Something Else we have not yet considered.  Report this to Administration."
)))))

and looks like this in the Formula editor (easier to see what is going on)



Just pass in your date field where I have my 'Activity Request Date' and it should work.

Change the quarters to suit your fiscal or calendar or contractual needs.

This formula sets Oct, Nov, Dec - Quarter1 (1st)
Jan, Feb, Mar - 2nd
etc.

But this will only allow you to choose a quarter for your filter.  You'll have to do some more teaching to get the system to know which quarter is 'current' automatically.

There is probably an easier way.  Maybe someone will teach us both something.  :)

Thanks,

~Rob
Photo of Lisa

Lisa

  • 210 Points 100 badge 2x thumb
I have  this formula duration field for the annual birthday question:

var date thisyear = Date(Year(Today()),Month([Birth Date]),Day([Birth Date]));
var date nextyear = Date(Year(Today()) + 1,Month([Birth Date]),Day([Birth Date]));

If($thisyear >= Today(),
  $thisyear - Today(),
  $nextyear - Today())

Similarly, this can be used to flag the quarter if the quarter of 'Start date' is the same as the quarter of Today():

var number startqtr = If(Month([Start Date])<3,1,If(Month([Start Date])<6,2,If(Month([Start Date])<9,3,4)));
var number thisqtr = If(Month(Today()) < 3,1,If(Month(Today()) < 6,2,If(Month(Today()) < 9,3,4)));

If($startqtr = $thisqtr,1)


Photo of Jim

Jim

  • 40 Points
Thanks everyone for the comments and help. Part of the struggle is that we want to be able to show the Month/Date and the current year... I was able to accomplish this in a horrifically inefficient way, but here goes my solution:

Created a "DOB" [Date Field] (Technically, Optional)
Created a "Month of DOB" [Text Field]
Created a "Date of DOB" [Text Field]
Created a "Today" [Date/Time Formula Field - Now()]
Created a "Year" [Text Formula Field - Year(ToDate([Today]))]
Created a "Current Year DOB" [Text Formula Field - [Month of DOB]&"-"&[Date of DOB]&"-"&[Year]]
Created a "Current Year DOB01" [Date Formula Field - ToDate([Current Year DOB])]

For Next Year:
Create a "Next Year" [Numeric Formula Field - ToNumber([Year])+1]
Create a "Next Year DOB" [Text Formula Field - [Month of DOB]&"-"&[Date of DOB]&"-"&[Next Year]]
Create a "Next Year DOB01" [Date Formula Field - ToDate([Next Year DOB])]

I'm going to replicate this for the anniversary need. 

I appreciate everyone's help!