Count in formula counting 1 when dates are the same?

[Anonymous]

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

21/05/2015    Prospect

Any idea why it does this please?

[marcus_sommer]

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

- Marcus

[Kushal_Chawda]

try below

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

[Anonymous]

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

[Anonymous]

Hi Marcus,

No there is no relation.

[Kushal_Chawda]

like this

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

[marcus_sommer]

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

[Anonymous]

Hi, that more than doubles the figures now

[Anonymous]

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.

[settu_periasamy]

May be try with TOTAL option

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