Forum Discussion

TonyGonzalez's avatar
TonyGonzalez
Qrew Trainee
4 months ago

Fiscal Year as a dynamic filter

I know where the FY settings are for an Application and how to set it.  I also know how to use it on a Chart under the filter settings.  

But, I don't want to filter my chart by Fiscal Year.  Rather, I want my FY field to be a dynamic filter.  So, how do I create a field, and the formula, to put in FY based on another date field.  

Specific example, we have a Customer Sat Survey.  I can potentially use either date field.  Date Created or (user inputed) Date of Contact.  I want a FY field that looks at this specific date and assigns it to a Fiscal year and then puts that year in the field.  I can then designate that field as a dynamic filter.



------------------------------
Gonzo - A Newbie, but learning fast. (Former SmartSheet evangelist who is quickly converting to QuickBase)
------------------------------

3 Replies

  • yes, you can use a formula to calculate the fiscal year.   Can you tell us what your rules are for the fiscal year start with an example?



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • TonyGonzalez's avatar
      TonyGonzalez
      Qrew Trainee

      Mark,

      Of course.  We start our FY Oct 1 and the FY # is the following year.  i.e. Oct 1, 2023 is the start of the FY 24 year.  



      ------------------------------
      Gonzo - A Newbie, but learning fast. (Former SmartSheet evangelist who is quickly converting to QuickBase)
      IT Director City of New Braunfels
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion

        Here is one examnple that calculates to a formay like FY24

        var number FiscalYear = If(Month([Invoice Date])>=10, Year([Invoice Date])+1, Year([Invoice Date]));
         
        "FY" & Right(ToText($FiscalYear),2)
        or else this would be numeric
        If(Month([Invoice Date])>=10, Year([Invoice Date])+1, Year([Invoice Date]))


        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------