topic No errors but not getting any results in KPI in App Development

No errors but not getting any results in KPI

pgloc2020 — Thu, 14 Dec 2023 23:40:15 GMT

Hello all,

I have to count claims based on the following conditions in the KPI. I have used the following expression, it did not display any error but it did not display any counts on the front end.

Count({< TYPE = {'Sound Recording'}, DECISION = {'Approved', 'Rejected'}, CREATED_YEAR = {"<= 1972"} >} CLAIM_NUM)

Count({< TYPE = {'Sound Recording'}, DECISION = {'Approved', 'Rejected'}, CREATED_YEAR = {"<= '1972' "} >} CLAIM_NUM)

What I am missing here.

'Created Year" column has just "Years" in it and it is listed as VARCHAR in the database.

Thank you all in advance.

Re: No errors but not getting any results in KPI

rwunderlich — Fri, 15 Dec 2023 00:35:36 GMT

If you replicate these selections in the App manually using filters, does a simple Count(CLAIM_NUM) return anything?

-Rob

Re: No errors but not getting any results in KPI

pgloc2020 — Fri, 15 Dec 2023 02:44:11 GMT

In order to count <= 1970, user has to select so many years. Using manual filters option might not be feasible option. I am getting correct counts when I use

Count({< TYPE = {'Sound Recording'}, DECISION = {'Approved', 'Rejected'} >} CLAIM_NUM)

but I have to count all the claims from <= 1972 and using that in the expression, I am not getting any data in the KPI. I have a feeling because Created Year is listed as a VARCHAR, I need to convert it into the numbers in order to use < and = .

Is there any way I can filter the whole data within the Load statement so that it will load only 1972 and prior to that records only?

Re: No errors but not getting any results in KPI

RamanaKumarChintalapati — Fri, 15 Dec 2023 05:18:45 GMT

Hi @pgloc2020 ,

Hey here you can use below where condition in Data Load editor Script

MyTable:
LOAD * FROM ...MySource...
WHERE Year(MyDateStampField) >= 1972;

Hope it will help you!!!

Thanks!!!!

Re: No errors but not getting any results in KPI

rwunderlich — Fri, 15 Dec 2023 14:26:14 GMT

I did not mean for you to have the user select all the years. I was suggesting the manual selections for you only as a way to test that your set expression should return some data.

You should simulate the YEAR modifier by typing <=1970 in the YEAR filter (a numeric search, see https://community.qlik.com/t5/Design/Qlik-Sense-Cheat-Sheet-version-2-0/ba-p/1667455)

-Rob

Re: No errors but not getting any results in KPI

pgloc2020 — Fri, 15 Dec 2023 14:33:21 GMT

Hi,

I tried as you mentioned in the Load statement, but no data is displaying on the sheet. It is coming as blank.

where Year([CREATED_YEAR])<= 1972;

Re: No errors but not getting any results in KPI

pgloc2020 — Fri, 15 Dec 2023 15:04:21 GMT

My updates- I got the answer. Here's what I did

In the load statement I used num#([CREATED_YEAR],'####') as [CREATED_YEAR] and it converted the data (4 digit) into the numbers leaving bad data as string.

In the set analysis I used the following which gives me the correct counts

Count({< TYPE = {'Sound Recording'}, DECISION = {'Approved', 'Rejected'}, CREATED_YEAR ={"<=1972"} >} CLAIM_NUM)

Following link helped me to get the answer-

https://www.datameer.com/blog/greater-than-less-than-and-dates-in-qlik-set-analysis/