Nov 04, 2022 06:47 AM
Hoping for some help!
I have a column with a date in, and two other columns, one with a start date and one with an end date,
I want to do a conditional / IF rollup that brings me through a value if the date in question falls between the start date and end date in my other columns
grateful for any help!
Nov 04, 2022 07:04 AM
Sorry to be a bit cleaer, I have Table A that has multiple entries in it related to each customer and the money we have charged them. this table has the date we charged the customer in it as a field
I then have Table B where I have each customer and month listed as an entry with other data about that customer and I want to rollup and match what I charged that customer in that month. This is the table that has the fields with the start dates and end dates in.
Nov 04, 2022 08:22 AM
I’m still not really clear on what the data in your tables represents, which makes it hard to envision the solution.
Based on what you’ve described, I’d imagine you have a table for
Customers (a record for each customer), and a table for
Charges (a record for each charge), but this “Table B” you are describing doesn’t sound like it fits either one of those. What kind of data are you wanting each record in “Table B” to represent?
Nov 04, 2022 02:25 PM
Very fair - I wrote this use case in a rush and will take a little more time and care over it @Jeremy_Oglesby
My tables (amongst others):
In table 3 (charge months) I would like to reference table 2, and for each record (which represents a month of the year per customer) any charges raised in that month (as noted from the charge date in table 2), I would like all of those charges summed and rolled up and attributed to that ‘charge month’ if the charge dates in question fall between the start and end date of that record/month in table 3.
If this is still not clear enough I thank you for your patience and can provide screenshots etc.
Nov 04, 2022 02:50 PM
Ok, thanks @Ben_Crouch, I think I’ve got it now.
I think you’d be doing yourself a favor to link your
Charge Months rather than directly to
Customers. You can still pull the
Customer into the
Charge through the
Charge Month act as a mediator between
Charge, the problem largely solves itself. The
Charge Month now just needs to roll-up its own
Charges to get a sum.
Is there any reason establishing your linked relationships in that way wouldn’t work for your use-case?
Nov 05, 2022 01:44 AM
I’m going to look into that Monday. I think the drawback there is we would need to manually link each charge to the appropriate charge month. Charges are populated automatically from our invoice software, so someone would regularly need to go to the table and look each charge up and link it. I hate having humans in the loop :slightly_smiling_face:
Will assess Monday and report back.