Skip to main content

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






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

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…?

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.



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



Thank you

Reply


Terms & Conditions