Help with formula to capture 2018 results as well as 2019

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
I have this formula below that is capturing data just fine for current month and next month numbers, however 2018 numbers are not getting captured because this formula doesn't account for previous year. Can Some offer some assistance? My brain is jelly.


Photo of Todd

Todd

  • 110 Points 100 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
Todd, please copy and paste and post the actual code.  I can't copy and paste to suggest edits to a screen shot, plus its too small to read.

Or else please courier some reading glasses to me.
Photo of Todd

Todd

  • 110 Points 100 badge 2x thumb


If(
(Upper([Line]) = "LIFE") and (Year([Issue Date]) = Year(Today())) and (Month(Today()) - Month([Issue Date]) = 1) and ($Red), "Red",
(Upper([Line]) = "LIFE") and (Year([Issue Date]) = Year(Today())) and (Month(Today()) - Month([Issue Date]) = 1) and ($Yellow), "Yellow",
(Upper([Line]) = "LIFE") and (Year([Issue Date]) = Year(Today())) and (Month(Today()) - Month([Issue Date]) = 1) and ($Green), "Green", ""
)
Photo of Todd

Todd

  • 110 Points 100 badge 2x thumb
LOL oh Mark..glasses on the way..NOT

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
try this


var bool IssueDateWasLastMonth =
IF(FirstDayOfMonth([Issue Date]) = FirstDayOfMonthAdjustMonth(Today(),-1),true); 

// the above will be true if the 1st of the month of the issue date is the same date as the first of the month of the month previous to today.

IF(
Upper([Line]) <> "LIFE","",

$Red,"Red",
$Yellow,"Yellow",
$Green, "Green")





Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,806 Points 50k badge 2x thumb
I see I missed part of my formula!

IF(
Upper([Line]) <> "LIFE","",
not $IssueDateWasLastMonth,"",

$Red,"Red",
$Yellow,"Yellow",
$Green, "Green")