Sum Distinct

[Anonymous]

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

[er_mohit]

Have you tried either

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

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

[simondachstr]

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)

[Anonymous]

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)

[Anonymous]

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

[simondachstr]

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

[Anonymous]

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

[Anonymous · Accepted solution]

Found good-enough solution:

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

thanx for your help