Forum Discussion

JaimeStrawderma's avatar
JaimeStrawderma
Qrew Cadet
6 years ago

Formula Numeric Fields with Conditions

I have a field called Year to Date Sales. I also have a field called Reporting Year. I have a report that needs to bring in that Year to Date Sales figure into the appropriate year i.e., 2019, 2020, etc. I have the formula numeric field of 2019 with the formula [Year to Date Sales] and it returns that value just fine. What would the formula be so that when Reporting Year changes to 2020 the field 2020 returns Year to Date Sales for 2020? Thank you in advance.
reports and charts
Tables and fields
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    This is where an application variable can really help.  You build an application variable with the value of the current year.

    I normally label my variable CurrentYear (no spaces) which helps me separate other fields which typically have normalized spacing in them.

    Then, in a formula you can reference the variable value as a regular field [CurrentYear].  So, for example; you would write a formula:

    If(Year([Year to Date Sales]) = [CurrentYear], [Year to Date Sales], null)

    This would then allow a formula field to dynamically change each time you change the application variable year when the calendar rolls around.  I also use this in reports and saves a lot of extra customization.
  • JaimeStrawderma's avatar
    JaimeStrawderma
    Qrew Cadet
    6 years ago
    My Year to Date Sales field is a formula numeric because it is a sum of fields Q1-Q4. When I add your formula, the Year to Date Sales is highlighted and the warning of "Expecting date but found number". I also tried If(Year[Reporting Year] = [CurrentYear],[Year to Date Sales], null) but it didn't like that either.
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    6 years ago
    It sounds like your Q1, Q2, Q3 and Q4 fields need to be filtered on the current year; not the [Year to Date Sales] field which is a formula. This is probably something we can best resolve via a quick screenshare.  Call me at 626 771 0454.