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?

[jcampbell474]

There must not be enough data in the sample or I scrambled something I shouldn't have.  I'm toggling between all dates and 9/13 in the full app and it works every time.  Also works on other dates with data.

I can try to create another (working) sample.

[sunny_talwar]

That would be useful

[vishsaggi]

There are missing dates in DateQuoted in your model any specific reason for this ? Let us know just wondering ?

[jcampbell474]

Sunny and Vish, the attached file should be more usable.

Couple of notes:

- The # Matches and % Match columns work in the application when data is not scrambled.  I left them in the sample because they are needed in the Quote and Sold columns.  If there is a match, I need to display how many quotes/sold within the next 7-days.

- The sample is filtered to 9/13 - which is just one day where Quote & Sold populate.  They will populate on other days, too. 

Again, I thank you both for the help!

[jcampbell474]

Vish, I posted an updated sample that should include all needed data - the date you referenced.  I don't think the initial sample was complete.

Thanks!

[vishsaggi]

Ok. Will look into it. Just correct me, I am a little lost, so for those two fields Quote <=7Days and sold<=7Days you want the count even when there is no selection right ?

[jcampbell474]

Thank you.  Yes, you are correct.  I need it to count with and without a date selection.

[sunny_talwar]

With selection numbers are correct?

Also, you were equating agPhoneNum = {'callPhoneNum'}, but agPhoneNum include numbers. Are you trying to equate it to another field name?

[jcampbell474]

That's right - with selection, numbers are correct.

agPhoneNum and callPhoneNum both include numbers and often match.  I have to match them first, then do the counts, sums, etc...  No, I'm not trying to equate either field to another field name.

[sunny_talwar · Accepted solution]

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))