topic Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model in QlikView

Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

vvira1316 — Tue, 05 Sep 2017 17:08:48 GMT

Hi,

I've a straight table with following two dimensions Original API and Table Name

I've attached sample data set with simple data model, but it is not same in my actual scenario. I've included a screen shot of it for understanding. It will be difficult for me to provide that here. If required I can try to replicate it.

Screen shots of Actual Table and KPI Calculations

Portion of Data Model for above can be seen in screen shot below

Thanks,

Vijay

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

sunny_talwar — Tue, 05 Sep 2017 17:24:16 GMT

Well one is a distinct count the other one is not.... May be you need Sum(Aggr()) here

=Sum({<[Table Name]>}Aggr(Count(DISTINCT {<[Table Name]>} [Original API Name]), [Original API Name], [Table Name]))

or DISTINCT Count [Original API Name]&[Table Name]

=Count(DISTINCT {<[Table Name]>} [Original API Name]&[Table Name])

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

rwunderlich — Tue, 05 Sep 2017 17:37:22 GMT

Why aggr()? Just removing the DISTINCT makes both the table and the KPI return the same value.

-Rob

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

sunny_talwar — Tue, 05 Sep 2017 17:38:40 GMT

I was not sure why use DISTINCT in the first place... may be there is a reason to use it... but yes, you are right... removing DISTINCT should do it in this case

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

vvira1316 — Tue, 05 Sep 2017 17:40:26 GMT

Hi Sunny,

Yes, both expressions are working. Thanks.

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

vvira1316 — Tue, 05 Sep 2017 17:46:01 GMT

Removing distinct provides higher count.

=Count({<[Table Name]>} [Original API Name]&[Table Name])

Count is 3544

it may be due to values of other dimensions within those two tables from the actual data model.

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

vvira1316 — Tue, 05 Sep 2017 17:46:43 GMT

Both these expressions provided expected count of 1103.

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

sunny_talwar — Tue, 05 Sep 2017 17:49:02 GMT

Not removing DISTINCT from this

=Count({<[Table Name]>} [Original API Name]&[Table Name])

This should use DISTINCT

=Count(DISTINCT {<[Table Name]>} [Original API Name]&[Table Name])

Rob mentioned about removing DISTINCT from this

=Count(DISTINCT {<[Table Name]>} [Original API Name])

But I am not sure what you are really doing and that is why I didn't suggest this to you in the first place

Re: Straight Table count vs Count as KPI is not matching when additional dimnesions are present in the data model

vvira1316 — Wed, 06 Sep 2017 14:56:29 GMT

Yes, I had tried that before posting the question. It gives me count of 3544. In this dashboard user is trying to use transactional information with metadata of tables where that information is getting stored. In order to do all that I had to juggle data model and data processing to achieve the functionality the user wanted. I've been able to achieve all of that so far. This count was off which I had noticed so I wanted to take care of it and I was having challenge to get it working.

Thanks to you and Rob for helping us out.

Best Regards,

Vijay