Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
That would be useful
There are missing dates in DateQuoted in your model any specific reason for this ? Let us know just wondering ?
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!
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!
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 ?
Thank you. Yes, you are correct. I need it to count with and without a date selection.
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?
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.
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))