Include dynamic calculation on a Set analysis

_CD_ — Wed, 17 Nov 2021 15:04:41 GMT

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.

Re: Include dynamic calculation on a Set analysis

marcus_sommer — Thu, 18 Nov 2021 11:45:18 GMT

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:

Count({<DateStart={"<=[RangeDateFinish]"}, DateFinish={">=[RangeDateStart]"}>} distinct Client) *
-(count(DateAnalysis)=1)

Depending on your requirements you may need to apply the date-conditions to the second-part, too.

- Marcus

topic Re: Include dynamic calculation on a Set analysis in App Development

Include dynamic calculation on a Set analysis

Re: Include dynamic calculation on a Set analysis