topic Re: Conditional sum in set analyse in QlikView

Conditional sum in set analyse

jozisvk11 — Tue, 28 Mar 2017 13:14:50 GMT

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.

Re: Conditional sum in set analyse

aarkay29 — Tue, 28 Mar 2017 13:34:26 GMT

Try this

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

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

Re: Conditional sum in set analyse

sunny_talwar — Tue, 28 Mar 2017 16:40:57 GMT

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

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

Re: Conditional sum in set analyse

Anonymous — Tue, 28 Mar 2017 16:59:41 GMT

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

sunny_talwar — Tue, 28 Mar 2017 17:07:39 GMT

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?

Re: Conditional sum in set analyse

Anonymous — Tue, 28 Mar 2017 17:15:37 GMT

it depends

Re: Conditional sum in set analyse

sunny_talwar — Tue, 28 Mar 2017 17:18:56 GMT

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

Re: Conditional sum in set analyse

jozisvk11 — Wed, 29 Mar 2017 06:01:38 GMT

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

Kushal_Chawda — Wed, 29 Mar 2017 07:58:33 GMT

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

sunny_talwar — Wed, 29 Mar 2017 10:05:19 GMT

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