0

Return sum of values between two given dates.

have appointment dates and sales on a a table in one line. E.g 10/09/2019.....£45.00 each entry is relevant to individual customers. I would like to run a report on all sales in a given time frame (between 2 dates that I enter). In excel formulas I think this would be achieved using the SUMIF function, but I cannot see a way to do this in Ninox. Any helpers?

9 replies

null
    • Nick
    • 4 yrs ago
    • Reported - view

    I'm using this approach:

    New table named 'Report Filter' with fields

    Account - Reference to Accounts table

    From - Start date

    To - End Date

    View - displaying the filtered records, with this code

    ---

    let t := this;
    let a := Account;
    select Entries where Account = a and Date >= t.From and Date <= t.To

    ---

    and a Summary field, formula with this code:

    ---

    let t := this;
    let a := Account;
    sum((select Entries where Account = a and Date >= t.From and Date <= t.To).Amount)

    ---

    Change the field and table names to match yours (i.e. Accounts -> Customers and Entries -> Invoices).

    The result is

    • Scott_Williams
    • 4 yrs ago
    • Reported - view

    Oh dear. I cannot understand this. I got so far but failed. The data I have is in a sub table and consists of results from other tables. So, I have an appointment date and then a sales value based on a calculation. I want a new form where I can enter a start date and an end date and then return the sum of all sales from all customers between those dates, taking the data from the sub table.

    • Nick
    • 4 yrs ago
    • Reported - view

    I've sent you an email with a small example db.

    • Sal
    • 4 yrs ago
    • Reported - view

    Hi! Please it's possible to have the example db?

    Thanks!

    Best regards

    • Nick
    • 4 yrs ago
    • Reported - view

    Sure!

    • Rogers_Muldrow
    • 3 yrs ago
    • Reported - view

    Nick is it possible that I can get this template as well?

    • Nick
    • 3 yrs ago
    • Reported - view
    • VEZIROGLU CONS. CO.
    • KADRI_SAMIM_VEZIROGLU
    • 3 yrs ago
    • Reported - view

    Is it possible to change the above code to include a running total column (cumulative) for each day of transactions between the selected dates for a chosen field. Any help would be appreciated, thank you.

    • Nick
    • 3 yrs ago
    • Reported - view

    @KADRI SAMIM VEZIROGLU

    This is the code for general Running Balance (formula field in Journal table):

    -

    let xCat := Category.number(Id);
    let xDate := Date;
    sum((select Journal where Category.number(Id) = xCat and xDate >= Date).Impact)

    -

    For what you are specifically asking for, I have not yet found the solution... 

    Μay someone more specialized give us some help?

Content aside

  • 3 yrs agoLast active
  • 9Replies
  • 2172Views