Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Both records have an association to ContactOptions which are not '' ?
- Marcus
try below
if(ContactOptions <> '', Sum(aggr(NODISTINCT Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType), Date)))
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
Hi Marcus,
No there is no relation.
like this
if(ContactOptions <> '', Sum(aggr(NODISTINCT Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>}EngagementType), DateEmpFormAdded)))
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
Hi, that more than doubles the figures now
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.
May be try with TOTAL option
Count(TOTAL <EngagementType> {$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType))