KPI Count not matching straight table

[qlikfresh]

Hey guys 

I have a KPI counting the number of times a result status appears in my straight table however the KPI count is different to when I go in and manually count the filtered items in the table

For example: if I filter for RESULT_STATUS = 'C' (complete) the KPI returns 17 when there are only 5 results in the table 

Have tried the following formulas: 

=sum(aggr(if(RESULT_STATUS='C',1,0), ID_COLUMN))

=(Count(distinct{<RESULT_STATUS = {"C"}>} ID_COLUMN))

Really not understanding how this can even be possible especially with using set analysis, any help much appreciated 

[GaryGiles]

You need to use single quotes in set analysis.  Based on your description, this should work:

=Count(distinct {<RESULT_STATUS = {'C'}>} ID_COLUMN)

[qlikfresh]

Hey @GaryGiles I've made that change and the KPI still doesn't add up with the results in the straight table 

Is there any other way you can think of that I'm perhaps missing? 😞 

[agigliotti]

Hi @qlikfresh ,

To help understanding more, could you specify dimensions and measures of your straight table as well as the expression are you using in the KPI?
What is your actual result and the expected one?

Best Regards
Andrea

[qlikfresh]

Hi @agigliotti 

Sure - in the straight table I have data generated from our labs. The labs wish to have a KPI at the top displaying the number of samples that are still pending/ outstanding i.e. every sample that RESULT_STATUS='C' (complete but not authorized) 

So, when filtering for result status C the KPI is displaying a number e.g. 17 but there may only be 6 samples in the straight table with a result status of C.

Ideally the KPI would = filtered results in the straight table - as this provides a clear high level view of #samples left without having to scroll through entirety of the table 

Thanks!

[MayilVahanan · Accepted solution]

Hi @qlikfresh 

=sum(aggr(if(RESULT_STATUS='C',1,0), ID_COLUMN))

You need to include all dimension in aggr function, not only ID_Column.

[qlikfresh]

Hey @MayilVahanan 

Thanks for your response. If you see above that was one formula I had already tried but to no avail! 😞

The KPI returns 17 when there is only 5 results in the straight table. It really has me baffled 

[MayilVahanan]

HI  @qlikfresh 

Can you share the sample app.

how many dimension u are using?

[qlikfresh]

Hey @MayilVahanan 

The table has 13 dimensions and 2 measures. There are no joins or associations in this particular table, the goal is to literally just filter the entire dataset to see what is not authorized and have that as a count KPI on the dashboard 🙂 

[simonaubert]

Hello,

Can you please  :
-clarify the good answer (5 or 17?) and if pending means Complete?

-make a straight table with =(Count(distinct{<RESULT_STATUS = {"C"}>} ID_COLUMN)),the RESULT_STATUS  and Status column ?

Thanks