Count Distinct or Count Aggr doesn't return correct values

[amber2000]

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

[swuehl]

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 ))]

))

[amber2000]

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)

[swuehl · Accepted solution]

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.