Max value for start date when two conditions macthed

[VincentD1]

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

[Anil_Babu_Samineni]

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

[vincent_ardiet_]

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

[VincentD1]

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.