Count if criteria met and within date range

[jcampbell474]

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?

[sunny_talwar]

Jason -

Would it be possible to share a sample to look at your issue?

[vishsaggi]

Can you try this ?

= Count({< agPhoneNum = {'callPhoneNum'} , DateQuoted ={">=$(=sDate) <=$(sDate7)"} >} DISTINCT AgtID)

[jcampbell474]

Thank you, Vish.  For some reason, it doesn't work at all - with or without a date selected.

[vishsaggi]

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 ?

[jcampbell474]

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.

[jcampbell474]

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.

[rupamjyotidas]

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

[jcampbell474]

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.

[sunny_talwar]

On which date selection does it still work?