

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count if criteria met and within date range
I'm trying to count the number of times (Distinct) AgtID is listed if the agPhoneNum=callPhoneNum AND the DateQuoted is between sDate & sDate7. (sDate7 is simply sDate + 7 days, defined in the script) I'm not using any variables here.
Here is what I have, which works ONLY when a date is selected.
Count({<agPhoneNum = {'callPhoneNum'}>+<DateQuoted={">=$(=sDate)<=$(sDate7)"}>} AgtID)
I need something that will work even if a date is not selected. It currently just zero's out. The first column is Date in my chart (straight table), so date is a dimension.
What am I missing??? Why does a date have to be selected for the expression to work?
- « Previous Replies
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The numbers are slightly off, but it seems that what I have makes more sense
Quote <= 7 Days
=Count(DISTINCT Aggr(If(DateQuoted >= sDate and DateQuoted <= sDate7, AgtID), AgtID, sDate, DateQuoted, sDate7, UserName))
Sold <= 7 Days
=Count(DISTINCT Aggr(If(DateBound >= sDate and DateBound <= sDate7, AgtID), AgtID, sDate, sDate7, DateBound))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jason -
Would it be possible to share a sample to look at your issue?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you try this ?
= Count({< agPhoneNum = {'callPhoneNum'} , DateQuoted ={">=$(=sDate) <=$(sDate7)"} >} DISTINCT AgtID)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, Vish. For some reason, it doesn't work at all - with or without a date selected.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Can you share a sample as Sunny asked, we need to check date formatting for those date fields? Would it be possible to share a sample please ?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sunny, I tried to trim the app down & scramble, but I'm unable to. Telephone # is part of the expression and when I scramble it, the expression no longer works.
Here are a few addt'l expressions I tried:
//count(aggr(if(sum(if(agPhoneNum=callPhoneNum,1))>0,1), AgtID))
//Count({$<agPhoneNum={'callPhoneNum'}, DateQuoted={'>=$(=sDate)<=$(sDate7)'}>} DISTINCT AgtID)
//count({<DateQuoted={'>=$(=sDate)<=$(=sDate7)'}>} AgtID)
//count({<agPhoneNum={'=$(=callPhoneNum)'},DateQuoted={'>=$(=sDate)<=$(sDate7)'}>} AgtID)
//count(aggr(if(sum( if(agPhoneNum=callPhoneNum,1))>0,1),callPhoneNum))
//count({<DateQuoted={'>=$(=sDate)<=$(sDate7)'}>} AgtID)
All of the date field formats match. I subtracted them in a text object and they calculated correctly. They all display in the MM/DD/YYYY format. They accurately convert to number if needed.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sunny & Vish, thank you again for helping.
I was able to trim down the application. If you have time to review the attachment, it will be greatly appreciated.
I'm solely looking at the Quote <=7 days and Sold <=7 days expressions.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe this is just repeating, as everyone has done that, check the date format, try to bring the format to same,
Use Date(sDate,'MM/DD/YY') or whatever format you use for both the dates. Hopefully your agPhoneNum and callPhoneNum are in same format i.e. Text or Number


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It works perfectly, but only if a date is selected. Telephone numbers match, dates calculate/compare, etc... Remove the date selection and all of the expressions still work - with the exception of this one:
Count({<agPhoneNum = {'callPhoneNum'}>+<DateQuoted={">=$(=sDate)<=$(sDate7)"}>} AgtID)
I'm guessing it needs an absolute reference when using DateQuoted, instead of many field values.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
On which date selection does it still work?

- « Previous Replies
- Next Replies »