Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Sum Distinct

Hi All,

I have this table

Cat NumberLotWeight KgUnit Quanty
Unit Type
1234
12501Drum
212505Drum
35050Kg
4200200Kg
Total1750256-

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Found good-enough solution:

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

7 Replies
Master II

Have you tried either

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

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

Luminary Alumni

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
Not applicable
Author

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
Not applicable
Author

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

Luminary Alumni

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

Anonymous
Not applicable
Author

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
Not applicable
Author

Found good-enough solution:

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