Find the most recent date of a set

Hi everyone,

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?

Here’s a link to an example of what i’m doing

Thank you!

Hi @Tim_Richardson1,

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.

I see you point.
there is an error in my formula: I’m working on your doc and post the correct one.

Hi @Tim_Richardson,

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.

Anyway: am I getting closer?

[edit]: Yep, apparently it’s still an open topic: More Duration Options (Especially Years/Months)

1 Like

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?

Thanks for all your help!!

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).

Cheers

1 Like

Sorry, didn’t mean to say my issue was with multiple columns. I get the logic of making and just hiding them when needed.

It’s my specific formula in the “Bands” column I added it to the above coda document. It’s a nesting IF formula that doesn’t work for some reason.

Here’s the formula:

IF(thisRow.[Years Since]<1,“1 Year or Less”,IF(thisRow.[Years Since]>1,“1-3 Years”,“Over 3 Years”))

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.

2 Likes

Ahhh gotcha, this answers it. Thank you!! I really appreciate your help!

1 Like

:+1:t2::+1:t2::+1:t2:
Great we made it, in the end :wink:

1 Like

Hi @Tim_Richardson1,

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).

Enjoy it :slight_smile:

4 Likes

Thank you! That’s perfect, I’m updating all my switch formulas to this

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:

5 Likes

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.

Good one :+1:

1 Like

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

2 Likes

Yes it is a good idea most of the times.
At first it doesn’t but as the doc grows in complexity you see the benefits.

2 Likes

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…

1 Like