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!