I have an expenses chart here with link
1, I already filtered dates
2, and within those dates filter the expenses that are over the “minimum spend” column (so the current button should filter the dates AND minimum spend)
3, column “sum of filtered expenses” - SUM of all filtered expenses after clicking button “filter” by row
4, new column - “target spend” minus column “sum of filtered expenses”
Thanks so much!
You don’t have a minimum spend column, a target spend column, nor a button so I’m not sure exactly what you need.
Can you add in and populate the minimum/target spend columns, and make a note where you want your button and what it should do?
Can you talk us through your approach on some of this stuff? E.g. why are you calculating column rank? Why does filtering need a button? (all of the other columns should be able to auto-update as soon as any data changes, without the need for a button)
i have in Table 1 on top. a “Target” column, a “Min spend” column, a “remaining” column.
the button “Filter” filters the expenses of Table Sergio within the start and end dates in Table 1.
I need that button to filter also the expenses that are OVER the amount in the “min spend” column. I.e. if min spend is 500. the button should filter the expenses 500 or over 500 dollars only.
after these expenses are filtered, I want all the expenses have be added together and shown in the “sum of filtered expenses” column.
the “remaining” column will be the “target” column amount minus the “sum of filtered expenses” column amount.
I hope this is clear. i’m not sure how many buttons i need so i’m open to suggestions.
hi Nick. i don’t need the rank column (somehow the Coda help person added it for me)
as for filtering, i’m filtering the dates and also expense amount according to row.
Sorry Christina, I’m really struggling to understand what exactly it is that you want.
Can we go right back to basics on this so that I can understand what you’re trying to achieve?
So far, I’ve understood:
- You want a list of expenses, showing who incurred it, when, what, and how much the expense was
- You want to be able to view a list of expenses per employee, and you want to be able to filter this based on date
- You want to apply some sort of filter based on something to do with target and minimum spends, and then sum up only the rows that match this filter
What I don’t understand and need to clarify before we can move forwards:
- What is the minimum spend, and do you want to see expenses that are higher or lower than this?
- What is the target spend, and do you want to see expenses that are higher or lower than this?
- What are the ‘promos’ and how do the expenses link to the promos?
- Why are you trying to add a column showing filtered expenses to the promos table when there’s no apparent link between expenses and promos?
maybe i should explain more this is a chart to calculate expenses for airline miles promotions.
Each promotion will have a set date range and minimum spend (i.e. each transaction needs to be 500 or above), and I will need 38000 of total expenses during that time period.
i want to log ALL my employees expenses, i will input all expenses no matter large or small, and it will be rolling for the whole year.
Therefore i need to filter the expenses in relation to the promotion criteria and i want to know how much more i need to spend to fulfil the promotion for airline miles.
this is an example:
Oct 29, 2018 - 5000 (not counted since out of date range)
Dec 2, 2018 - 600 (counted since inside date range and over 500)
Dec 3, 2018 - 450 (not counted, since lower than minimum spend)
Dec 6, 2018 - 1000 (counted since inside date range and over 500)
Sum of filtered expenses till now = 600+1000 = 1600
Target spend is 38000
Remaining = 38000-1600 = 36400
in table 1, the rows will have different promotions as the dates and min spend might change or have overlapping dates. I wonder if that is clear? so the next promotion might be starting Mar 1 - May 30. Min spend is 0 and target is 10000.
If this is not clear pls let me know!
- What is the minimum spend, and do you want to see expenses that are higher or lower than this? I have put in the row, the minimum spend is 500, so anything at or above 500 have to be shown.
- What is the target spend, and do you want to see expenses that are higher or lower than this? target spend is a fixed number which all my expenses over need to meet. so i have to spend 38000 between dates dec 1 to Feb 28 with expenses over 500.
- What are the ‘promos’ and how do the expenses link to the promos? Promo is just a name. so for the first row, the name of the promo is HSBC. the dates are listed start and end date. only expenses at or above 500 will be counted, and i need to spend 38000.
- Why are you trying to add a column showing filtered expenses to the promos table when there’s no apparent link between expenses and promos? i don’t know how to link them that’s why i need help!
How about this?
Everything is fairly straightforward and spreadsheet-y (e.g. subtract one column from another), except for the “Current Spend Total” column. That one needs the following formula:
sum([All Expenses].Filter(Date>=[Start Date] AND Date<=[End Date] AND Amount>=[Min Expense Value]).Amount)
To break that down,
- Calculate the sum of a bunch of rows. Which rows?
- Grab the “All Expenses” table
- Filter it so that we only get the rows that (1) have a date on or after our start date AND (2) have a date on or before our end date AND (3) have an Amount that’s at least as big as our Minimum Expense Value
- That will leave us with a group of rows that match our filter. But since we’re doing a sum, we’re not interested in the whole row, we just want the “Amount” column from each of those rows. So we specificy “.Amount”
One thing that might be helpful to understand is that filtering for the purposes of math (like we’re doing here), and filtering for the purposes of seeing a table view (like how you can see just Sergio’s expenses at the bottom), are kind of separate in Coda. For example, you can’t make things easier on yourself by first creating a table view that’s filtered to what you want, and then do math on top of that. You need to build your filtering into the formulas for your columns.
great nick! let me try this!
your work is genius! just wondering one more step is that i want to click a button (on the row) to filter all the expenses related to that specific promo. i’m stuck as i have no idea how to program the button.
can you help with that?