topic Re: Count the Number of Daily Records while Excluding Certain Values in App Development

Count the Number of Daily Records while Excluding Certain Values

gzapantis1 — Wed, 20 Jul 2016 19:33:37 GMT

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

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

maxgro — Wed, 20 Jul 2016 20:02:10 GMT

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)

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

gzapantis1 — Wed, 20 Jul 2016 20:11:02 GMT

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.

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

maxgro — Wed, 20 Jul 2016 21:58:56 GMT

try

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