Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Grouping customers chronologically by treatment times. Can you solve this?

Topic Labels: Formulas
676 4
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi,

I am struggling to solve this problem.

I have several date ranges that represents treatments for my customers.

I would like to group the customers/treatment chronologically, so that I can see the how many customers I have each month.
I could do this by creating a formula for the year DATETIME_FORMAT({Seuraava jakso alkaa},'YYYY') and one for the month DATETIME_FORMAT({Seuraava jakso alkaa},"MM - MMMM")

table.PNG
There are two problems with this:

  1. This formulas are showing always only the next treatment of a customer.
  2. If next treatment time changes, of course the past treatments ar not appearing in the group anymore.

Problem to solve:
Could it be possible to have in the group all the treatments (past and future) chronologically by a customer (start date is OK, don´t have to be a range)?

I hope this is understandable as I’m not a native English speaker.
Thank you in advance, any help will be most appreciated.

Cheers,
Attila

4 Replies 4

You need to move your treatment times into their own table that links to your customers table.

Each record in this treatment table would have JUST ONE start time and JUST ONE end time.

Then, you could do your grouping and sorting and filtering from the treatments table instead of the customers table.

Thank you for the reply.

Could you be more specific, please? If I understood well, I´ll need to create a table for 1. treament, 2. treatment… and so on…?

You can accomplish this with two tables. One holding customers, and one holding treatments.

Then, as @ScottWorld said, you can group/sort/filter from the [Treatments] table. Example in the below screenshot shows the data on the [Treatments] table grouped by Year, then by Month.

Treatments

Actually, in the meantime I have figured it out exactly the same way as you wrote :slightly_smiling_face:

Thank you