# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Contributor

## Conditional sum in set analyse

Hello, i need to help with set analyse. I have table:

ID transaction          Item          Quantity          Value

10                      M2000         5                    100

10                      M2000          5                    10

20                      M2000          20                  200

20                      M2000          20                  20

30                      M2000        50                   500

30                      M2000         50                   50

40                      M2000         30                  300

I  need to result. (Quantity should be max value according to ID transaction and Value si sum of all rows). I would like to write it in set analyse.

Result table:

Item          Quantity          Value

M2000         105              1180

Thank you.

Tags (1)
1 Solution

Accepted Solutions
MVP

## Re: Conditional sum in set analyse

another way

Data:

LOAD *, AutoNumber([ID transaction]&Item) as Key Inline [

ID transaction,Item,Quantity,Value

10,M2000,5,100

10,M2000,5,10

20,M2000,20,200

20,M2000,30,20

30,M2000,50,500

30,M2000,50,50

40,M2000,30,300 ];

Left Join(Data)

max(Quantity) as MaxQuantity

Resident Data

Group by Key;

quantity, sum(aggr(Sum(distinct MaxQuantity),key))

value , Sum(Value)

9 Replies
Valued Contributor

## Re: Conditional sum in set analyse

Try this

quantity=sum(Aggr(Max(Quantity),[ID transaction]))

value= sum(Aggr(Max(Value),[ID transaction]))

MVP

## Re: Conditional sum in set analyse

I think value will be Sum(Value) and may be this for Quantity

Sum(Aggr(FirstSortedValue(Quantity, -Value), [ID transaction], Item))

Valued Contributor III

## Re: Conditional sum in set analyse

... should work, but I would use firstsortedvalue always in combination with distinct or make sure, that the first sorted value is unique...

MVP

## Re: Conditional sum in set analyse

I don't like using DISTINCT, because in case there are two options, the FirstSortedValue() will randomly choose one of the two options.... do you want to have that or use another method?

Valued Contributor III

it depends

MVP

## Re: Conditional sum in set analyse

exactly.... that's why not using DISTINCT here... because I don't know how this is getting used here

Contributor

## Re: Conditional sum in set analyse

My firts set analyse is quite complicated:

sum ({< [ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'} >} [Quantity]).  How can I add yours expression in my Set analyse ???

1. Aar - quantity=sum(Aggr(Max(Quantity),[ID transaction]))  or

2. Sunny - Sum(Aggr(FirstSortedValue(Quantity, -Value), [ID transaction], Item))

Can I add it to Set analyse ??

MVP

## Re: Conditional sum in set analyse

another way

Data:

LOAD *, AutoNumber([ID transaction]&Item) as Key Inline [

ID transaction,Item,Quantity,Value

10,M2000,5,100

10,M2000,5,10

20,M2000,20,200

20,M2000,30,20

30,M2000,50,500

30,M2000,50,50

40,M2000,30,300 ];

Left Join(Data)

max(Quantity) as MaxQuantity

Resident Data

Group by Key;

quantity, sum(aggr(Sum(distinct MaxQuantity),key))

value , Sum(Value)

MVP

## Re: Conditional sum in set analyse

Based on private conversation, it seems that this worked for the OP

Sum({<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'}>} Aggr(FirstSortedValue({<[ID account] = {"501386", "501387", "501388", "501389", "501390", "501391", "501392", "501396"}, [Object] = {'whinr110'}, [Division] ={'701', '702', '703', '723'}>} Quantity, -Value), [ID transaction], Item))