topic KPI not matching straight table in App Development

KPI not matching straight table

qlikuser22 — Wed, 04 May 2022 08:24:18 GMT

I have a KPI for calculating number of orders.

=Count ({$<Status={Finished}>} ID) - This KPI gives 25900

I am creating a table with 28 columns and no filter applied. But if I download the table into excel I can see only 20875 records. I have a dimension called status where I am using the expression if(Status = 'Finished', 'Completed'). I am not writing any expression in other dimensions.

I want to know where I am losing around 5000 records.

Please do help.

Thanks.

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:03:31 GMT

Hi Qlikuser22,

You have to aggregate the data by these 28 dimensions using Aggr and then apply count function as below. Then you would get exact result what are you getting in straight table.

Aggr(Count ({$<Status={Finished}>} ID), your 28 dimensions seperated by comma). I hope this helps.

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:05:13 GMT

Sum(Aggr(Count ({$<Status={Finished}>} ID),dimensions seperated by comma))

Re: KPI not matching straight table

qlikuser22 — Wed, 04 May 2022 09:11:57 GMT

Hi @vish123

Thanks for the quick response.

Should I use this expression in a table for Status dimension?

Because, my KPI is correct with 26000 records. I want the same in my table .

Please let me know if I should write the expression in table Status dimension??

Thanks.

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:11:27 GMT

Hi Qlikuser22,

If that is the case, you can ignore all dimensions in table by using {1} in set analysis of count expression.

Count (1{$<Status={Finished}>} ID).. try this

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:13:10 GMT

Try this as well .

Count (Total {$<Status={Finished}>} ID)

Re: KPI not matching straight table

qlikuser22 — Wed, 04 May 2022 09:16:43 GMT

Thanks, this gives the KPI correct. But how do I get the number of rows in the table matching with KPI value?

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:19:07 GMT

Hi Qlikuser22,

Can you please give me screen shot where you wanna show number of records in a table? what expression you are trying to use within the table?

Re: KPI not matching straight table

qlikuser22 — Wed, 04 May 2022 09:24:47 GMT

This is my KPI. I am just creating a table with 28 dimensions like

Customer Name

Order No

Starting Date

Working Date

Completed Date

Status // I am writing a single expression here as if(Staus='Finished','Finished')

Address

City

Country

and so on..

Here, my KPI is correct with 30943. I want my table should also have 30943 rows.

Thanks a lot

Re: KPI not matching straight table

vish123 — Wed, 04 May 2022 09:30:39 GMT

Hi Qlikuser22,

Any expressions in a straight table such as count or sum will be calculated based on dimensions added. In this case i suggest to check for any null values or add all other dimensions to get the desired output. Count expression is giving the count for overall dimensions. Hope this helps.

Please close this thread if you feel helpful. so that others can use it as solution.

Re: KPI not matching straight table

qlikuser22 — Wed, 04 May 2022 09:33:03 GMT

Perfect. I understood that all the dimensions has to be added to match with KPI value.

Thanks.