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

[sunny_talwar]

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

[swuehl]

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

[amber2000]

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.

[sunny_talwar]

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

[amber2000]

This is the example with the wrong values.

Both charts show different results and both are wrong results

[swuehl]

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.

[sunny_talwar]

What is the correct output you are hoping to see?

[swuehl]

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.

[amber2000]

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