Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ))]
))
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)
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.