Hi all,
I've got a problem to resolve that seem quite insoluble for me.
I work with data formated like that in my model :
| Line |
Client |
DateStart |
DateFinish |
DateAnalysis |
| 1 |
A |
01/01/2021 |
31/01/2021 |
- |
| 2 |
A |
01/02/2021 |
15/02/2021 |
15/02/2021 |
| 3 |
A |
16/02/2021 |
31/03/2021 |
31/03/2021 |
| 4 |
B |
01/01/2021 |
31/01/2021 |
- |
Easy level :
I need to count clients on a date or on a range date.
For a Single Date, I used a syntax in order to keep only 1 line per client => The line containing the [SelectDate].
Count({<DateStart={"<=[SelectDate]"}, DateFinish={">=[SelectDate]"}>} distinct Client)
Example for the 10/01/2021, I will keep the line 1 and 4.
For a Range Date, I keep all line containing a date in the [RangeDate].
Count({<DateStart={"<=[RangeDateFinish]"}, DateFinish={">=[RangeDateStart]"}>} distinct Client)
Example for the 10/01/2021 to 10/02/2021, I will keep the line 1, 2 and 4.
This works fine.
Hard level :
Same start (I need to count clients on a range date) if there's only 1 distinct DateAnalysis per client in the [RangeDate].
(For example I will have another analysis if there's 2 distinct DateAnalysis per client in the [RangeDate])
Example for the 10/01/2021 to 31/01/2021, result will be 0 (Work on line 1 and 4, no DateAnalysis).
Example for the 10/01/2021 to 10/02/2021, result will be 1 (Work on line 1, 2 and 4, Client A have 1 DateAnalysis, B have 0 DateAnalysis).
My problem is to add this dynamic calculation per client criteria.
I can't do something like :
Count({<DateStart={"<=[RangeDateFinish]"}, DateFinish={">=[RangeDateStart]"}, count(DateAnalysis)={1}>} distinct Client)
I think it's impossible but I can't find "how" or "how change my data" do this.
I will take any help and be very thankfull ! Don't hesitate to ask me for more information. It's not very clear.
