
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HELP! set analysis with 2 dates
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!
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@sidhiq91 thank you so Much!
I'll try it and tell you later. 🙂

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
Please close the thread by marking correct answer & give likes if you like the post.
