No errors but not getting any results in KPI

[pgloc2020]

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)

or

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. 

[rwunderlich]

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

-Rob

[pgloc2020]

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? 

[RamanaKumarChintalapati]

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!!!! 

[rwunderlich]

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

[pgloc2020]

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;

[pgloc2020]

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/