Assistance with an expression

[Anonymous]

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%

[Gysbert_Wassenaar · Accepted solution]

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])

[Anonymous]

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?

[Gysbert_Wassenaar]

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

[Anonymous]

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!

[Gysbert_Wassenaar]

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.

[Anonymous]

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.

[Gysbert_Wassenaar]

{$<[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.