Discussions

Expand all | Collapse all

QB Formula to categorize months

  • 1.  QB Formula to categorize months

    Posted 20 days ago
    I have the following summary report:


    What I would really like to do is somehow get the row grouping (Dispatch Date) to be based off of the following groupings: Current Month, Previous Month, Next Month, All Others.

    What would be the most efficient way to get this done? Currently, I am looking at creating a few extra fields in the table and calculating the current month and the dispatch date relevant to the current month for all the records in the table.


    ------------------------------
    Niraj Shah
    ------------------------------


  • 2.  RE: QB Formula to categorize months

    Posted 20 days ago
    Try this
    var date DDFirst = FirstDayOfMonth([Dispatch Date]);

    IF(
    $DDFirst = FirstDayOfMonth(Today()),                               "1. Current Month",
    $DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",
    $DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),  "3. Next Month",
                                                                                                        "4. All Others")

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: QB Formula to categorize months

    Posted 18 days ago
    Thanks, Mark. This worked well and I was able to include it in the report formula area.

    I am now facing a scenario where my "All Others" section includes dates 2 months onwards but also more 2 months previously. How would I update the formula to change "4. All Others" to "4. 2 Months Onwards" that also excludes anything more previous than the last month?

    Thank you.


    ------------------------------
    Niraj Shah
    ------------------------------



  • 4.  RE: QB Formula to categorize months

    Posted 18 days ago
    IF(
    $DDFirst = FirstDayOfMonth(Today()),                               "1. Current Month",
    $DDFirst = FirstDayOfMonth(AdjustMonth(Today(),-1)), "2. Previous Month",
    $DDFirst = FirstDayOfMonth(AdjustMonth(Today(),1)),  "3. Next Month",
    $DDFirst >= FirstDayOfMonth(AdjustMonth(Today(),2)),  "4. Two Months Onwards")


    I was  not clear about this "ask" about this 

    "that also excludes anything more previous than the last month?"

    The formula above will calculate to null if it's not a 1,2,3 or 4.

                                                                                                 

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------