Fields in Set Analysis

[#1]

I am attempting to use two date fields in a set analysis. I have formatted these date fields to be comparable...ie (mm/d/yyyy).

The expression that I am attempting is below.

Count({$<CreateDate={"<=StartDate"}>} Distinct OrderIds)

I have not had any luck producing a correct result

Any Suggestions?

Thanks!!

[Oleg_Troyansky · Accepted solution]

This set analysis condition could only work if you had a single available value of a StartDate.

Remember that Set Analysis conditions are evaluated "globally", e.g. outside of the specific chart dimensions. That being said, you can't compare CreateDate with the Start Date, if the StartDate is one of your Dimensions (or derived from one).

You can try something like the following:

Count({$<CreateDate={"=CreateDate<=StartDate"}>} Distinct OrderIds)

However, if both dates are in the same table, I'd rather recommend calculating a flag in the script and using the flag in your expression. Something like the following:

In the script:

If(CreateDate<=StartDate, 1, null()) as Created_Start_Flag,

...

In the expression:

Count({$<Created_Start_Flag={1}>} Distinct OrderIds)

Looks much more straight-forward to me...

good luck!