Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
jozisvk11
Creator
Creator

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.

1 Solution

Accepted Solutions
Kushal_Chawda

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)

View solution in original post

9 Replies
aarkay29
Specialist
Specialist

Try this

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

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

sunny_talwar

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

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

Anonymous
Not applicable

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

sunny_talwar

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?

Anonymous
Not applicable

it depends

sunny_talwar

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

jozisvk11
Creator
Creator
Author

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

Kushal_Chawda

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)

sunny_talwar

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