HELP! set analysis with 2 dates

[nlucia]

Hello everybody!

I need help with the following expression.
I've 2 DATE fields.

1. RESOLUTION_DATE
2. CLOSING_DATE

Firts, I need that if there a RESOLUTION DATE, give me the RESOLUTION DATE and if not, give me the CLOSING DATE.
THIS IS OK = If(resolution_date,resolution_date,closing_date)

But with this, I need count(distinc( opportunities)) where If(resolution_date,resolution_date,closing_date) =2021.

I tried with this but it doesn't work.

Count({1<resolution_date.autoCalendar.Year={'2021'} >*< [closing_date.autoCalendar.Year]={'2021'}>} distinct OpprId)

Can you Help Me?
thanks a lot!

[sidhiq91 · Accepted solution]

@nlucia Instead of trying the entire thing in Set Analysis, I would suggest you another Idea.

In the Script level, try Coalesce() and Emptyisnull() functions, these 2 will return a first NON Null Values and you can use that Date in your Set Analysis. Please see below my script:

In the Back End:

NoConcatenate
Temp:
Load * Inline [
Resolution Date, Closing Date
,12/31/2022
];

NoConcatenate
Temp1:
Load Year(Date) as Year;

Load *,
Coalesce(EmptyIsNull([Resolution Date]),EmptyIsNull([Closing Date])) as Date
Resident Temp;

Drop table Temp;

Exit Script;

In the front end:

Count(distinct {<Year={'2022'}>}opprId)

If this resolves your issue, please like and accept it as a solution.

[nlucia]

@sidhiq91  thank you so Much! 

I'll try it and tell you later. 🙂

[MayilVahanan]

HI 

Try like this in script

Load Alt(resolution_date,closing_date) as date, * from ursource;

You can create master calendar based on date. 

Then In front end, as suggested by sidhiq91, you can use set analysis

Count(distinct {<Year={2022}>}opprId)
or

Count(distinct {<Year={$(=Year(Today()))}>}opprId)