I’m creating a mini-CRM and I want to see a volunteer’s most recent contribution to our program. They can participate in panels - e.g. Feb 2014, March 2015, August 2018. The details for these panels, including their dates, is in a separate table connected by lookup. I want to order volunteers by their most recent panel participation. My idea right now is to create a column with the formula that finds the most recent from a set (from above example, Aug 2018). Then a column that calculates the number of months between now and that date. Then sorting the list by that # of months. I can’t figure out a formula for that first step though - find the most recent date. Is there such a thing?
Is this what you want? Today() - thisRow.Panels.Sort(column:Date).Last().Date
The first part - thisRow.Panels.Sort(column:Date).Last() - is doing exactly what you are asking for (i.e. the most recent date of a set). I also added the difference from. today
Thank you Federico for this suggestion! I want the formula to ultimately show the # of months since their last panel participation. Satinda should be 9 years, Lesley should say a few months, Jim should say 4 years. Does that make sense?
I find that a lot of my Coda questions come from not being able to wrap my head around the various ways you reference specific cells in tables. For some reason I just can’t get my head around the language, it doesn’t quite make sense to me. This is even after I’ve watched all the tutorials and whatnot.
Let me know if I’m getting closer (doc is public, so everyone can access to it).
Basically, you’re asking a duration in years, months and days that I have roughly constructed.
I didn’t check if this is a topic already asked: let me browse a bit.
Yes this is exactly what I was after! I didn’t know about the sort command - how does it know to sort it by recent dates, and thus have [last] be most recent?
The final step is for me to band the types into three. I’m using an IF within an IF to do this, but it isn’t working out - see the ‘bands’ column. Is there maybe a better way to do that?
Great!
Ok, Sort() function parameters are: ascending/descending order and the field you want to sort: so you just say what field has to be considered (Date, in this case).
As per the “extra-columns”, you can simply hide them.
It’s not a bad idea to have intermediate computation results in columns.
Not only it allows to you to perform quicker bug-fixing, but it’s also a good approach in term of performance (have a look at: https://help.coda.io/en/articles/3031872-improving-performance).
Think of additional columns as variables (if it makes sense for you).
Hi @Tim_Richardson,
Sorry, perhaps I misunderstood your question (I didn’t see the Bands column).
In your formula you’re not taking into consideration “boundaries conditions”.
You are considering <1 or >1 (not =1): which is why the condition falls in the default.
I added a column with this formula: IF(thisRow.[Years Since]<1,"1 Year or Less", IF(thisRow.[Years Since]>=1 && thisRow.[Years Since]<=3, "1-3 Years","Over 3 Years")) let me know if it’s clear.
I just forgot something (yesterday I was fighting with my doc and I was a bit distracted…): instead of nested IFs, in these cases the SwitchIf() Function comes at hand: SwitchIf( thisRow.[Years Since]<1,"1 Year or Less", thisRow.[Years Since]>=1 && thisRow.[Years Since]<=3, "1-3 Years", "Over 3 Years")
Aside form being more readable, it’s also easier to add more bands (in your example).
Hello there! I see that you are all set up but I just wanted to add my solution.
Personally I try to avoid using Switch or SwitchIf formulas because it is harder to change when you want to add new data so here it is:
Totally agree with the solution proposed by @Saul_Garcia!
It is btw what I usually do myself: decouple into “configuration” tables what are usually business/functional decision rules.
Thank you for this! Would you say it’s a good principle of database/coda design to separate complex formulas into columns or tables? I tend to be quite sparing with additional tables, but I don’t think I have a good reason for that
This is a great help article that explains in more depth the benefits of splitting up some formulas and which ones have a greater affect. It’s a lot to take in, but a good reference to check out if you’re curious about this…
