topic Re: Count Distinct or Count Aggr doesn't return correct values in QlikView

Count Distinct or Count Aggr doesn't return correct values

amber2000 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm having difficulties to use a Count distinct() or Count Aggr()

My table haas duplicate values on column SD_Dest_Pallet.

I want to count the Distinct SD_Dest_Pallet's per Date/Aisle_Facturation/Shop_Ens/

Even the simple sum of SD_Shipped_Colli isn't correct, I'll always get 5 colli to much

In my example (that's attached) I'm using only 1 Aisle_Facturation and 1 Shop_Ens tot test the expressions.

I've a chart with a distinct(Count) and another chart with a Aggr (count)

These are the correct values that should be presented in Qlikview:

SD_Close_Date	Aisle_Facturation	Shop_Ens	#Colli	#Pallet	Colli / Pal Uit
20160331	PBL_OVERIGE	Pricelist_Hyper	401	5	80,20
20160401	PBL_OVERIGE	Pricelist_Hyper	591	10	59,10
20160402	PBL_OVERIGE	Pricelist_Hyper	341	5	68,20
20160404	PBL_OVERIGE	Pricelist_Hyper	396	5	79,20
20160405	PBL_OVERIGE	Pricelist_Hyper	1015	6	169,17
20160406	PBL_OVERIGE	Pricelist_Hyper	328	5	65,60
20160407	PBL_OVERIGE	Pricelist_Hyper	50	0	0,00
TOTAL			3122	36	86,72

Does anyone see where the problem is situated in my example please???

Monique

Re: Count Distinct or Count Aggr doesn't return correct values

sunny_talwar — Thu, 07 Apr 2016 10:31:20 GMT

You aggr solution seems to be working, not sure what the issue is?

Re: Count Distinct or Count Aggr doesn't return correct values

swuehl — Thu, 07 Apr 2016 10:33:27 GMT

Doesn't the Aggr() chart show exactely these numbers?

Re: Count Distinct or Count Aggr doesn't return correct values

amber2000 — Thu, 07 Apr 2016 10:43:48 GMT

Sorry Guys,

I gave the wrong example, that's a simple chart and there it works fine.

In a complex chart It's another story.

I'll send a correct attach of the complex example showing the wrong values.

Re: Count Distinct or Count Aggr doesn't return correct values

sunny_talwar — Thu, 07 Apr 2016 10:45:17 GMT

Ya unless we are able to see the issue, it would be difficult to propose a solution

Re: Count Distinct or Count Aggr doesn't return correct values

amber2000 — Thu, 07 Apr 2016 10:47:49 GMT

This is the example with the wrong values.

Both charts show different results and both are wrong results

Re: Count Distinct or Count Aggr doesn't return correct values

swuehl — Thu, 07 Apr 2016 10:50:26 GMT

If you want to count something per multiple dimensions, you should use these dimensions in the aggr() dimensions as well...

= Count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }aggr(count([Aisle_Facturation]), SD_Dest_Pallet))

Add your charts dimensions to the aggr() dimension list.

And probably you need to use Sum() as your outer aggregation.

Re: Count Distinct or Count Aggr doesn't return correct values

sunny_talwar — Thu, 07 Apr 2016 11:04:45 GMT

What is the correct output you are hoping to see?

Re: Count Distinct or Count Aggr doesn't return correct values

swuehl — Thu, 07 Apr 2016 11:09:31 GMT

Yes, would be helpful to know the expected ouput.

Maybe this?

= Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }

aggr(

count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }[Aisle_Facturation])

, [$(=GetCurrentField( Aisle ))] ,[$(=GetCurrentField( Enseigne ))], [$(=GetCurrentField( Periods ))]

))

1) Sum as outer aggregation

2) set expression used in both aggregations (not sure if you need to modify

3) full list of dimensions to group by.

Do you also need a count distinct here?

Not sure, missing the expected result or full description of logic.

Re: Count Distinct or Count Aggr doesn't return correct values

amber2000 — Thu, 07 Apr 2016 11:13:14 GMT

The Output that I showed in my initial question:

SD_Close_Date	Aisle_Facturation	Shop_Ens	#Colli	#Pallet	Colli / Pal Uit
20160331	PBL_Overige	Pricelist_Hyper	401	5	80,20
20160401	PBL_Overige	Pricelist_Hyper	591	10	59,10
20160402	PBL_Overige	Pricelist_Hyper	341	5	68,20
20160404	PBL_Overige	Pricelist_Hyper	396	5	79,20
20160405	PBL_Overige	Pricelist_Hyper	1015	6	169,17
20160406	PBL_Overige	Pricelist_Hyper	328	5	65,60
20160407	PBL_Overige	Pricelist_Hyper	50	0	0,00

Re: Count Distinct or Count Aggr doesn't return correct values

swuehl — Thu, 07 Apr 2016 11:23:06 GMT

Why do you expect 10 for April 01? I do get 10 12 pallets:

Aisle_Zone	Aisle_Facturation	SD_Close_Date	Shop_Ens	SD_Dest_Pallet
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025174460
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025174477
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025174484
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025174569
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025224097
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232665
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232672
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232689
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232696
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232771
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232788
PBL_Overige	PBL_OVERIGE	20160401	Pricelist_Hyper	554001020025232795

edit:

This is the expression I am currently using:

= Sum({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> }

aggr(

count({$ <[SD_WH] -= {12}, [Aisle_Zone] -= {PBS_Containers, PBS_Displays}> } DISTINCT [SD_Dest_Pallet])

, [$(=GetCurrentField( Aisle ))] ,[$(=GetCurrentField( Enseigne ))], [$(=GetCurrentField( Periods ))]

))

Re: Count Distinct or Count Aggr doesn't return correct values

amber2000 — Thu, 07 Apr 2016 11:39:40 GMT

Ah that's because SD_Dest_Pallet 554001020025174460 and 554001020025174569 are already included in

SD_Close_Date 20160331. (a pallet can only be counted once)

Re: Count Distinct or Count Aggr doesn't return correct values

swuehl — Thu, 07 Apr 2016 11:58:34 GMT

I think you should fix this in your data model.

You can create a flag that indicates the first SD_Close_Date for each pallet, which you then can use to filter in your set expression.

You can create a temporary table with min SD_Close_date and the flag field grouped by an appropriate key (maybe just SD_Dest_pallet), then join this flag back to your facts table.

You can also consider modifying your data model so that you get read of your pallets duplicates, which should simplify your aggregation.

Since we don't know anything about your model or requirement in detail, it's hard to be more specific.