topic Re: Re: Sum Distinct in QlikView

Sum Distinct

Anonymous — Wed, 06 Aug 2014 09:13:38 GMT

Hi All,

I have this table

Cat Number	Lot	Weight Kg	Unit Quanty	Unit Type
1234	1	250	1	Drum
	2	1250	5	Drum
	3	50	50	Kg
	4	200	200	Kg
Total		1750	256	-

It's a pivot table and i'm trying to present the total when the detailed rows are collapsed, but only if the Unit Type is the same.

i wish to drop the total were the unit types are not the same

i use this sentance but it doesn't always work

Aggr(Sum(DISTINCT UnitQnty),CatNumber,UnitType)

apreciate any help

avner

Re: Sum Distinct

er_mohit — Wed, 06 Aug 2014 09:20:31 GMT

Have you tried either

Aggr(Nodistinct Sum(DISTINCT {1} UnitQnty),CatNumber,UnitType)

Aggr(Nodistinct Sum(total {1} UnitQnty),CatNumber,UnitType)

Re: Sum Distinct

simondachstr — Wed, 06 Aug 2014 09:27:28 GMT

How about you use the Only(UnitType) in the set modifier. If more than one UnitType is available to select, this will evaluate to null automatically and should not return any values.

Something like:

Aggr(Sum(DISTINCT {<UnitType={'$(=only(UnitType))'}>} UnitQnty),CatNumber,UnitType)

Re: Re: Sum Distinct

Anonymous — Wed, 06 Aug 2014 09:55:35 GMT

it didn't work but i think taht's the way...

show sum where Unittype=UnitType

i just can't find the syntax for the equasion:

Sum({$<UnitType={$(=UnitType)}>}UnitQnty)

Re: Re: Sum Distinct

Anonymous — Wed, 06 Aug 2014 09:57:41 GMT

thanks but it being back, the total of the CatNumber, regardless the Lot Number

Re: Re: Sum Distinct

simondachstr — Wed, 06 Aug 2014 09:58:08 GMT

Use ' around your $-expansion in the set modifier.

Re: Re: Sum Distinct

Anonymous — Wed, 06 Aug 2014 10:04:28 GMT

it works if i spacify the unit like 'KG' or 'DRUM', but not when i try and make that field dynamic.

guess the set modifier is still wrong

Re: Sum Distinct

Anonymous — Wed, 06 Aug 2014 11:55:24 GMT

Found good-enough solution:

if(maxstring(UnitType)=Minstring(UnitType),sum(UnitQunty))

thanx for your help