topic Re: Count in formula counting 1 when dates are the same? in QlikView

Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 06:49:24 GMT

i am using the following formula:-

(ContactOptions <> '', Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType))

If there are two rows with the same date (for eg below), Qlikview is only counting it as "1" rather than what it should be "2"?

E.g.

Date EngagementType

21/05/2015 Prospect

Any idea why it does this please?

Re: Count in formula counting 1 when dates are the same?

marcus_sommer — Wed, 11 May 2016 07:17:17 GMT

Both records have an association to ContactOptions which are not '' ?

- Marcus

Re: Count in formula counting 1 when dates are the same?

Kushal_Chawda — Wed, 11 May 2016 07:23:27 GMT

try below

if(ContactOptions <> '', Sum(aggr(NODISTINCT Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType), Date)))

Re: Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 07:42:17 GMT

Hi,

Doesn’t work but I can see that it’s a syntax error as “Date” at the end of the formula is underlined in red but I can’t see the syntax error

Re: Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 07:45:17 GMT

Hi Marcus,

No there is no relation.

Re: Count in formula counting 1 when dates are the same?

Kushal_Chawda — Wed, 11 May 2016 07:50:18 GMT

like this

if(ContactOptions <> '', Sum(aggr(NODISTINCT Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>}EngagementType), DateEmpFormAdded)))

Re: Count in formula counting 1 when dates are the same?

marcus_sommer — Wed, 11 May 2016 07:52:28 GMT

Then I do not understand why you ContactOptions <> '' queries? Another point is - are you sure that all entries within the field Date are really dates which could be compared with <>= against other dates and not strings?

- Marcus

Re: Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 07:57:17 GMT

Hi, that more than doubles the figures now

Re: Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 08:04:29 GMT

Hi Marcus. Your right that the issue definitely lies in the ContactOptions <> section because I tested without it and besides including the records where ContactOption field is blank (which I DON’T want to include) the formula does pick up the right amount.

The reason why I don’t want to pick up where the Option field is blank is because some users will fill in all details and then leave the “ContactOption” field blank. If that happens I do not want to include these records in my results.

Re: Count in formula counting 1 when dates are the same?

settu_periasamy — Wed, 11 May 2016 08:11:02 GMT

May be try with TOTAL option

Count(TOTAL <EngagementType> {$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType))

Re: Count in formula counting 1 when dates are the same?

marcus_sommer — Wed, 11 May 2016 08:19:28 GMT

Then your logic worked like expected?

- Marcus

Re: Count in formula counting 1 when dates are the same?

Anonymous — Wed, 11 May 2016 08:29:05 GMT

This is crazy. I thought I understood what was going on…. I’ve reloaded the data and now even without the “ContactOption <> ‘’” it counts all the same dates as one! Grrrrhhhhh

Re: Count in formula counting 1 when dates are the same?

marcus_sommer — Wed, 11 May 2016 08:36:10 GMT

Then have a look on the Date field - if they are all really dates.

- Marcus