Using Left() and Right() inside of Set Analysis

[GraysonStack]

Hi - I have a field [Date_Added] that is formatted as: MM/DD/YYYY

I need to create a set analysis getting the count of Distinct %EmpIDKey of users with a MM <= month(today()) and YYYY <= year(today())

Essentially this logic, but converted into a set analysis:

=if(Right([Date Added - Security], 4) <= year(today()),

if(Left([Date Added - Security], 2) < month(today()),

[Date Added - Security]))

[BrunPierre]

You cold rewrite as ff;

=Count(DISTINCT {$<[[Date Added - Security] = {"<= $(=Date(MonthEnd(Today()), 'MM/DD/YYYY'))"}>} %EmpIDKey)

[Or]

It sounds like you're trying to replicate https://help.qlik.com/en-US/sense/February2024/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

If you can add a field in script, adding that as a flag would be much easier and cleaner than dealing with it on the front end.

[marksouzacosta]

Hi @GraysonStack, 

Two quick tips:

1. You should never split/decompose your Date values, unless you have a very specific and rare use case. Use the approach recommended by @BrunPierre and @Or 
2. Looks like your %EmpIDKey field is a link field. It is not recommended to use link fields in Measures since it can return inaccurate/unexpected results. Replace it with some other field. This simple exercise of replacing link fields will make you understand much better your Data Model and your Business Logic.

Regards,

Mark Costa