KPI not matching straight table

[qlikuser22]

Hi

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. 

[vish123]

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.

[vish123]

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

[qlikuser22]

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.

[vish123]

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

[vish123 · Accepted solution]

Try this as well .

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

[qlikuser22]

Hi

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

[vish123]

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?

[qlikuser22]

HI

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

Customer Name

ID

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 

[vish123]

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.