Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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
@VincentD1 How is the validation for the expression? Is this Ended OK with variable result as it should? or any error.
Could you try to put your set analysis in the MAX too?
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.