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”
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.
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!
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.
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.