Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
jozisvk11
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

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)

LOAD Distinct Key,

     max(Quantity) as MaxQuantity

Resident Data

Group by Key;

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

value , Sum(Value)

9 Replies
aarkay29
Valued Contributor

Re: Conditional sum in set analyse

Try this

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

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

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))

robin_hausdoerfer
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...

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?

robin_hausdoerfer
Valued Contributor III

Re: Conditional sum in set analyse

it depends

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

jozisvk11
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 ??

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)

LOAD Distinct Key,

     max(Quantity) as MaxQuantity

Resident Data

Group by Key;

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

value , Sum(Value)

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))