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.
What you are trying couldn't be done with a classical set analyses which worked mainly like a selection. There may be ways to write such condition within a set analysis but it would be executed like an if-loop. Therefore it's syntactically and logically easier not to match both approaches else to combine them separately. I think I would tend to something like this:
