topic Max value for start date when two conditions macthed in App Development

Max value for start date when two conditions macthed

VincentD1 — Tue, 20 Feb 2024 07:40:57 GMT

Hello,

I'm trying to finalize a setAnalysis without success so far.
I have got two tables (THINGS and THINGS_STATES with a common id (ID_THING)).

A THING can have many states. The table contains the following fields:
-ID_THING
-THING_START_DATE
-THING_END_DATE
-THING_STATUS (2 possible values : ACTIVE or INACTIVE

I would like to count only the THINGS :

Start date before AND end date after vDateMax
OR Start date before vDateMax AND Status = Active (or End date is null)

I've built this setAnalysis:

count( distinct { < [THING_START_DATE] = {"<=$(=DATE([vDateMax]))"} , [THING_END_DATE] = {">=$(=DATE([vDateMax]))"}> + < [THING_START_DATE] = {"<=$(=DATE([vDateMax]))"}, THING_STATUS = {'ACTIVE'}> } aggr(max([THING_START_DATE]),ID_THING))

vDateMax is a var set from a date selected by the user.

The ending AGGR was meant to keep only the max THING START DATE when a THING has one status starting at the same date as its previous status ending date.

How can I achieve this ?

Thanks a lot

Re: Max value for start date when two conditions macthed

Anil_Babu_Samineni — Tue, 13 Feb 2024 13:45:29 GMT

@VincentD1 How is the validation for the expression? Is this Ended OK with variable result as it should? or any error.

Re: Max value for start date when two conditions macthed

vincent_ardiet_ — Tue, 13 Feb 2024 15:44:33 GMT

Could you try to put your set analysis in the MAX too?

Re: Max value for start date when two conditions macthed

VincentD1 — Tue, 20 Feb 2024 07:40:27 GMT

The expression was almost correct.

In fact, it was the "distinct" at the beginning of the SetAnalysis that was an error since it excluded THINGS that had the same THING_START_DATE.