Formula to returns a date range based on selection from a dropdown field

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

I am trying to set up a report that uses a specific date range to return a result. I want he date range that the report to use to be generated by a selection made in a drop down from the source records. So for instance, if the field "Billing Cycle" is set to Monthly, I want the report to return records where the tasks dates are during the current month.

So how do I set up the field that reads the "Billing Cycle" field to return the current Month if "Monthly" is selected from the dropdown?

Photo of Chris_MCD

Chris_MCD

  • 0 Points

Posted 3 years ago

  • 0
  • 1
It seems to me that you can make a report filtered where the [Billing Cycle] is equal to Monthly and a second filter where the [Task Date] "is during" the current month.
Photo of Chris_MCD

Chris_MCD

  • 0 Points
That is true but then you would have to run a separate report for "Monthly" billing cycles and then another for "Quarterly". The idea to get a field, or fields to give a date range so that I can set the report to read that date range, whether it be a month or a year. Does that make sense or am I completely crazy?
I'm sure that its possible if I can understand your goal here.

So let's say that you have a setup here you have some Tasks stamped as Monthly, some as Quarterly and some at Annually as for their [Billing Cycle].

Your definition of the report filter for  tasks stamped as Monthly  seemed t be to include them if the date was "During the current Month".

Is that the same with the Quarterly, ie the task date is within the current Quarter?  So, for example it is now February 2016, so the current Quarter is between January to March of 2016 and the current year is during 2016?  Is that what you are after but all on one report? perhaps grouped by [Billing Cycle]
Photo of Chris_MCD

Chris_MCD

  • 0 Points
Exactly!
Photo of Chris_MCD

Chris_MCD

  • 0 Points
I have been reading about Relative Date Ranges, thought maybe that would pertain to this?
OK, so if that is what you want, the you need to calculate a checkbox field called
[Task Date is during the current Billing cycle]

not tested for syntax, so if it does not pass the syntax editor, please post your formula and the error message .....
This formula uses formula variables  http://www.quickbase.com/user-assistance/Default.html#formula_variables.html?Highlight=formula variables
here is the formula.  The formula for Quarterly  could be wriiten more tightly but I will do it the brute force way.:


var number M = Month([Task Date]);
var number T = Month(Today());

Case ([Billing Cycle],
"Monthly",  FirstDayofMonth(Today())=FirstdayOfMonth([Task Date]),

"Annually",Year(Today())=Year([TaskDate]),

"Quarterly", Year(Today()=Year([Task Date])
and
($M=1 or $M=2 or $M=3) and ($T=1 or $T=2 or $T=3)
or
($M=4 or $M=5 or $M=6) and ($T=4 or $T=5 or $T=6)
or
($M=7 or $M=8 or $M=9) and ($T=7 or $T=8 or $T=9)
or
($M=10 or $M=11 or $M=12) and ($T=10 or $T=11 or $T=12) )