Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
EMaebe
Contributor III
Contributor III

Set Analysis Question - Count Distinct with a date

I want to say the count distinct for OppId when StageName is close or implement, status is won, and the close date is less than or equal to today(). The tooltip says "OK", but I'm showing 0 count which I know is incorrect. 

Here's what I've got for the formula which is a hodgepodge of formulas I've found on the forum: 

Count(distinct {$<StageName={'Close','Implement'},Status={'Won'},ExpectedCloseDate<{"<$(=TimeStamp(Today(),'DD/MM/YYYY"}>} OppId)

 

I'm assuming it's something with the expected close date since when I take that portion out, I can get a count that's correct. Where did I go wrong? 

Labels (2)
1 Solution

Accepted Solutions
EMaebe
Contributor III
Contributor III
Author

Update2: It was my date format. It's working now: 

Count(distinct {$<StageName={'Close','Implement'},Status={'Won'},ExpectedCloseDate={"<$(=Date(Today(),'YYYY-MM-DD'))"}>} OppId)

Not sure what forum etiquette is when you solve your own problem. Do I mark my own solution as the answer? Do I delete? 

Update1: Never mind the below isn't working as I'd expect. If I filter on stagename, status, and expectedclosedate, it changes and matches with just the Count(distinct {$<StageName={'Close','Implement'},Status={'Won'}>} OppId). Not sure where I'm going wrong here. 

 

Original Reply (to myself):

I think I have it working with the following formula: 

Count(distinct {$<StageName={'Close','Implement'},Status={'Won'},ExpectedCloseDate={"<$(<Today())"}>} OppId)

View solution in original post

1 Reply
EMaebe
Contributor III
Contributor III
Author

Update2: It was my date format. It's working now: 

Count(distinct {$<StageName={'Close','Implement'},Status={'Won'},ExpectedCloseDate={"<$(=Date(Today(),'YYYY-MM-DD'))"}>} OppId)

Not sure what forum etiquette is when you solve your own problem. Do I mark my own solution as the answer? Do I delete? 

Update1: Never mind the below isn't working as I'd expect. If I filter on stagename, status, and expectedclosedate, it changes and matches with just the Count(distinct {$<StageName={'Close','Implement'},Status={'Won'}>} OppId). Not sure where I'm going wrong here. 

 

Original Reply (to myself):

I think I have it working with the following formula: 

Count(distinct {$<StageName={'Close','Implement'},Status={'Won'},ExpectedCloseDate={"<$(<Today())"}>} OppId)