Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

jcampbell474
Contributor III

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Count if criteria met and within date range

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

22 Replies
MVP
MVP

Re: Count if criteria met and within date range

Jason -

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

vishsaggi
Esteemed Contributor III

Re: Count if criteria met and within date range

Can you try this ?

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

jcampbell474
Contributor III

Re: Count if criteria met and within date range

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

vishsaggi
Esteemed Contributor III

Re: Count if criteria met and within date range

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
Contributor III

Re: Count if criteria met and within date range

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
Contributor III

Re: Count if criteria met and within date range

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
Valued Contributor

Re: Count if criteria met and within date range

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
Contributor III

Re: Count if criteria met and within date range

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.

MVP
MVP

Re: Count if criteria met and within date range

On which date selection does it still work?