Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gzapantis1
New Contributor

Count the Number of Daily Records while Excluding Certain Values

Hi,

I am having some trouble with my custom measures in Qlik Sense.  I am trying to count the number of records that were created today while excluding certain status codes. I am trying to exclude Status Codes 13 (deleted record) and 213 (test record), which should then result in a count of  all production records for the current day.  When I estimate the daily number of records, the deleted records and test records do not count towards the total.  I tried using nested IF statements, the Set Analysis Wizard, and a combination of both, all to no avail.

To limit to the current day...I have used the expression [IF(Date(xDate) LIKE Date(Now(0)),ID)] in the Expression Builder and the set analysis wizard gives me Count({$<StatusCode-={13},StatusCode-={213}>}ID) to remove the two status codes from the set and then count the number of records that remain (ID).

I know that I can easily use the Data Load Editor to exclude the status codes and then use the expression builder to limit the results to the current day, but I am trying to create a master app that can have separate sheets for production unit records and for test and deleted records.

Thank you,

Gerasimos Zapantis

1 Solution

Accepted Solutions
MVP
MVP

Re: Count the Number of Daily Records while Excluding Certain Values

try

count({$ <StatusCode=-{8,9}, datefield={'$(=today())'}>} ID)

3 Replies
MVP
MVP

Re: Count the Number of Daily Records while Excluding Certain Values

current date could be

count({$ <datefield={'$(=today())'}>} ID)

maybe you need a date to format the today as your datefield

exclude 2 status

count({$ <StatusCode=-{8,9}>} ID)

gzapantis1
New Contributor

Re: Count the Number of Daily Records while Excluding Certain Values

I have both parts working on separate sheets, but I am not able to combine them to have one sheet of just daily production records.  I have a sheet for daily records and a sheet for production records, but I do not have one sheet that takes both conditions into account.

MVP
MVP

Re: Count the Number of Daily Records while Excluding Certain Values

try

count({$ <StatusCode=-{8,9}, datefield={'$(=today())'}>} ID)

Community Browser