Forum Discussion

RyanLocke1's avatar
RyanLocke1
Qrew Cadet
3 years ago
Solved

Bi Monthly Billing Calculated Field based off Date chosen

Good Evening, I am new to quickbase and am trying to figure out a way to set up a filter where a user selects a date and then a report will filter for their current billing period. We bill bi-mont...
reports and charts
  • MarkShnier__You's avatar
    MarkShnier__You
    3 years ago
    Ryan

    I'm going to assume your field is calling [Invoice Date].  I'm typing on an ipad from a hotel room with a beer, so hopefully this will work. 


    Try this. Make two fields.  The first is called [Billing Period Start of Invoice Date]

    var date MyDate = [Invoice Date]; 
    var number DayNumber = IF(Day($MyDate) <= 15, 1, 16);

    Date(Year($MyDate), Month($MyDate), $DayNumber)

    // the formula above will return the date of the start of the Billing Period. 

    Then duplicate the field and call it [Billing Period Start of Today]

    but change the date field to be Today()

    var date MyDate = Today();
    var number DayNumber = IF(Day($MyDate) <= 15, 1, 16);

    Date(Year($MyDate), Month($MyDate), $DayNumber)


    now easy peasy, just filter the Report where those  two fields are equal. 

    Post back if you have have any problems. I'm cycling so I will be slow to respond. 




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
KorrieClark's avatar
KorrieClark
Qrew Trainee
3 years ago
What is the value you're calculating the timeframe on? Is it all records due on the 15th or the 31st or is it based on some form of date criteria already- i.e. contract start date, user input deadline?

It seems like when building a table you should be able to set criteria similar to the below based on what you're reference field is (i'm using deadline as an example):
Deadline = during= current= month
Deadline=during= past= 15 = days

To have on the homepage dashboard, it won't show a table that requires a "user select" date, you'd need to create a shortcut button to the full table if you want to sort based on something that the user selects uniquely each time.
You could however show the above formula and also add a criteria of something like "sales person= the current user" and it would show only records for that user with the additional criteria.

Hope I understood what you meant!

------------------------------
Korrie Clark
------------------------------