set analysis help

[Krish2459_58]

Hi,

I have to write this below IF caluse in set analyis. Could you please helop.

If caluse:

if((num([lease end date]) = num(Final_Term_Date)) or isNull(Final_Term_Date) and [continuing occupancy] = 'N',[Lease ref])

Thanks..

[marcus_sommer]

It's not possible because comparing two fields against each other requires a row-level evaluation but set analysis is a column-level evaluation. Further NULL doesn't exists and could be therefore not directly queried within a set analysis.

I suggest to consider to transfer the essential parts into the data-model which might be just:

coalesce(sign([lease end date] - Final_Term_Date), 2) as Flag

and then something like:

count({< Flag = {0, 2}, [continuing occupancy] = {'N'} >} [Lease ref])