Set Analysis Question - DateField1 >= DateField2

[Brysonds]

Hi All,

I have a pretty simple request to only show sales when DateField1 >= Datefield2. I tried a few different iterations of set analysis and either get the same amount as sum(sales) or get 0. Both date fields are in the same YYYY-MM-DD format

Example 1:

Sum({<DateField1 = {"$(= '>=' & DateField2)"}>} Sales) results in the same as Sum(Sales)

Example 2:

Sum({<DateField1 = {">=$(=DateField2)"}>} Sales) results in 0

Example 3: 

Sum({<DateField1 = {">=$(=Date(DateField2))"}>} Sales) results in 0

Any alternatives or ideas on how to get this working?

If I manually type in a date like this, it works fine:

Sum({<DateField1 = {">=$(=Date('2023-09-01'))"}>} Sales)

[Oleg_Troyansky · Accepted solution]

Hi,

It's important to realize that Set Analysis conditions are evaluated outside of your chart expressions, and hence you should formulate a condition that is "universal", or independent of your chart dimensions.

Also, your Set Analysis filters should be formulated correctly, to filter the correct data elements. In your case, you are not selecting Date1 values that are greater than Date2 values - that would give you the wrong result obviously. You are trying to select Sales for those rows of data where Date1>=Date2 (I presume that both dates reside in the same fact table).  With that in mind, a Set Analysis filter could look like this:

Sum( {<Sales = {"=DateField1>=DateFIeld2"}   >}  Sales)

Word of caution: in a relatively large data set, this will be extremely heavy! A lot heavier than using the dreaded IF function. So, you might find that an easier formula could work a lot faster:

Sum( IF(DateField1>=DateField2, Sales))

Ultimately the best performance could be achieved by adding a conditional flag field to your data, that would store the result of the date comparison:

LOAD
...
IF(DateField1>=DateField2, 1, 0)    as _DateFlag
...

and then your Set Analysis could be extremely simple, and it would work very fast:

Sum( {<_DateFlag={1} >}  Sales)

Cheers,

[Brysonds]

Thank you for the detailed response, this really helped me better understand how this was working. All of the proposed solutions worked!