Count the Number of Daily Records while Excluding Certain Values

[gzapantis1]

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

[maxgro]

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]

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.

[maxgro · Accepted solution]

try

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