Forum Discussion

UchennaOgbejesi's avatar
UchennaOgbejesi
Qrew Trainee
7 years ago

Issues with Conditional Logic and Month to Date

Hello everyone, I'm running into an issue with creating a report that is month to date  for only July, but for the rest of the fiscal year (August-June), I want to see the previous month (e.g. August would look at July, September would look at August, etc.). Our fiscal years end on June 30th and picks up on July 1st. Some context: We use Quickbase to track time worked on Projects and I would like to see time logged for the previous month, with the exception of July. This is my first time posting so let me know if I could provide any more context or details about my issue. I should note, I tried to use the conditional logic in one of my fields, but only have any or all isn't very helpful.
Formulas and functions
Relationships
reports and charts
  • ForrestParker's avatar
    ForrestParker
    Qrew Cadet
    7 years ago
    Uchenna, 
    You could create a formula checkbox field that is checked for the records that should be displayed and not for those that shouldn't.  You would use this field as the filter in your report.

    The formula would look something like the one below

    This field says, if we are in the month of July, and the month of the date field is July and it's year is the current year then show it.

    if it is January, and the date field shows December, and the Year in the date field is the previous year, then show it as well.

    On any other month, if the month of the date field is one less than the current month, and the year is the same as the current year then show it.

    If none of those conditions are met, then don't show it.

    I haven't totally tested it, but you can give it a try and see if it works.

    Show Record in Report Formula
    If  (  Month(Today())=7 and Month([Date])=7 and Year([Date])=Year(Today()),true  ,Month(Today())=1  and Month([Date])=12 and Year([Date])=Year(Today())-1,true  ,Month(Today())-1 = Month([Date]),true  ,false  )