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
-
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
-
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.
-
I've sent you an email with a small example db.
-
Hi! Please it's possible to have the example db?
Thanks!
Best regards
-
Sure!
-
Nick is it possible that I can get this template as well?
-
-
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.
-
@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
- 4 yrs agoLast active
- 9Replies
- 2215Views