topic Re: KPI Expression Doesn't Work Anymore? in App Development

KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 05:16:16 GMT

Hello Everyone!

So we have been working with Qlik Sense for a while now, and when we started to used to have the following expression in a KPI chart:

Count({<[ItemExpiryDate] = {"$(='<' & Date(Today()))"}>} distinct ItemID)

When we did a reload last week the value of the KPI chart is always equal to ZERO. We have checked the data and everything seems fine; however, I'd like to figure out why is Qlik Sense giving us this result. .

Your help is really appreciated,

Re: KPI Expression Doesn't Work Anymore?

lironbaram — Sun, 25 Dec 2016 06:51:48 GMT

check in what format the field ItemExpiryDate

it's important that the date format in the set analysis will be similar to the one of the field ItemExpiryDate

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 07:09:24 GMT

Thanks for your reply. I believe nothing has changed since we first implemented this expression. Also when we re-wrote the expression as an IF Condition it worked like a charm.

Thanks!

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 07:49:30 GMT

The operator should be outside

Count({<[ItemExpiryDate] = {"<=$(=Date(Today()))"}>} distinct ItemID)

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 07:55:13 GMT

Thanks Vineeth! However, the expression doesn't work. it gives the total of items. In other words, it ignores the condition.

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 08:00:28 GMT

what format is ItemExpiryDate?

if it is DD/MM/YYYY format then you need to add it to the expression as below

Both comparing field and value have to be in same format.

Count({<[ItemExpiryDate] = {"<=$(=Date(Today(),'DD/MM/YYYY'))"}>} distinct ItemID)

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 08:06:12 GMT

The format is the default, as it has not been changed. As I mentioned before, it was working fine and even when we re-wrote it as an IF condition (without specifying the format). It works find. Also I have tried specifying the format and the result now is "-" instead of a ZERO.

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 08:10:35 GMT

What is your Default Format?

try this

Count({<[ItemExpiryDate] = {'<=$(Date(Today()))'}>} distinct ItemID)

Count({<[ItemExpiryDate] = {"<=$(Date(Today()))"}>} distinct ItemID)

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 08:15:30 GMT

SET DateFormat='M/D/YYYY';

I have tried both expressions and it gives the total (ignores the condition)

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 08:16:42 GMT

and the Format for [ItemExpiryDate] is it the same?

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 08:18:25 GMT

Yes! and as I mentioned it was working before.

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 08:29:20 GMT

removing the equal sign here

Count({<[ItemExpiryDate] = {"<=$(Date(Today(),'M/D/YYYY'))"}>} distinct ItemID)

or could it be that your [ItemExpiryDate] is coming in as VarChar instead of Date??

Re: KPI Expression Doesn't Work Anymore?

Anonymous — Sun, 25 Dec 2016 08:47:30 GMT

Same result. The data type is DateTime.

Re: KPI Expression Doesn't Work Anymore?

vinieme12 — Sun, 25 Dec 2016 08:55:23 GMT

can you post a snapshot of your field [ItemExpiryDate]