topic Re: Assistance with an expression in App Development

Assistance with an expression

Anonymous — Sat, 16 Nov 2024 07:25:12 GMT

I have an expression, which works perfectly, except under one condition: Count({$<[Overall Status]={"Completed"}>} [GREW ID]) / Count([GREW ID])

The problem I have is that as soon as I place a filter on the Overall Status field my KPI's no longer work properly.

So the expression above divides the number of cases that have an Overall Status of Completed, against a total count of all cases giving me a percentage. Nice and simple.

If I filter my data by any data item other than Overall Status the KPI works exactly as it should.

My problem with this KPI/expression comes in when a user filters the overall status. For example, if the user decides they want to filter Overall Status to "Closed" I would expect the KPI to reflect 0%, after all there are no longer any cases with an Overall Status of Completed and so 0 divided by anything that is not 0 would result in 0...... but not with Qlik Sense. With Qlik Sense I get my KPI showing 933.8% for that specific example.

I've checked the raw data, there are no blank entries for Overall Status, and so no reason that I can see as to why this expression does not work.

My head is blown on this one, I have no idea why Qlik would list anything other than 0%

Re: Assistance with an expression

Gysbert_Wassenaar — Tue, 23 Oct 2018 13:44:24 GMT

Try this one: Count({$<[Overall Status]={"Completed"}>} [GREW ID]) / Count({$<[Overall Status]=>}[GREW ID])

Or this one: Count({$<[Overall Status]*={"Completed"}>} [GREW ID]) / Count({$<[Overall Status]=>}[GREW ID])

Re: Assistance with an expression

Anonymous — Tue, 23 Oct 2018 13:47:25 GMT

Thanks a lot, that seems to have fixed it. I'm relatively new to Qlik Expressions. Out of interest, what is your addition at the end actually telling Qlik?

Re: Assistance with an expression

Gysbert_Wassenaar — Tue, 23 Oct 2018 13:48:30 GMT

[Overall Status]= tells Qlik to ignore any selections in the Overall Status field.

Re: Assistance with an expression

Anonymous — Tue, 23 Oct 2018 13:49:48 GMT

Great, I'll store that away in my mind. Thank you for your help. Still don't get how Qlik got 933.8% instead of the correct 0%, but this works so I'm good to go!

Re: Assistance with an expression

Gysbert_Wassenaar — Tue, 23 Oct 2018 13:58:27 GMT

Well, you divide two numbers. The first is always the count of GREW ID with Completed as Overall Status. The second was the count of GREW ID with whatever Overall Status the user selected (no selection means all GREW ID's). Those two numbers are divided by each other, so if you get 993.8% you have a lot more GREW ID's that were completed then there where with whatever status the user selected.

Re: Assistance with an expression

Anonymous — Tue, 23 Oct 2018 14:00:44 GMT

Interesting. I thought that as the user had filtered the data to exclude Completed the expression would find 0 matches and mark accordingly.

Did not realise that it would kind of bypass the filter placed on the dataset.

Re: Assistance with an expression

Gysbert_Wassenaar — Tue, 23 Oct 2018 14:04:29 GMT

{$<[Overall Status]={"Completed"}>} means ignore any selection in Overall Status and use "Completed" instead. So it doesn't matter here what the user selects. The expression will always only count Completed.