Aggr by calculated field

[maxsheva]

Hi,

I need to aggregate values by two dimensions where one of them is calculated.

Is it possible to create?

LOAD * Inline [

GroupName, Value

Group1, 100

Group1, 300

Group2, 500

Group2, 900

Group2, 150

];

Expression is

Aggr(Sum(Value),GroupName

,if(Value<=100,1,if(Value<=500,2,3))

)

[effinty2112]

Hi Max,

               Combine the Pick and Match functions to give string values to the dimension.

New Dim=

Pick(

Match(-1,aggr(sum(Value),ID)<=100,aggr(sum(Value),ID)<=500,-1),// this line is as previous returning 1,2 or 3

'Huey','Dewie','Louie')

GroupName	New Dim	sum(Value)	Count(ID)
		1950	5
Group1	Huey	100	1
Group1	Dewie	300	1
Group2	Dewie	650	2
Group2	Louie	900	1

Kind regards

Andrew

[antoniotiman]

Like this ?

[maxsheva]

Would you please write an expression?

[antoniotiman]

As You see it

Pivot Table :

1 Dimension

1 Calculated Dimension

1 Expression

[maxsheva]

I use straight table. That's why I need to aggregate values by two dimensions but where one of them is calculated

[antoniotiman]

Straight Table

[maxsheva]

My mistake I didn't quite explain my goal.

I added Value2

LOAD * Inline [

GroupName, Value,Value2

Group1, 100, 10

Group1, 300, 20

Group2, 500, 10

Group2, 400, 20

Group2, 900, 15

Group2, 150, 10

];

I need sum(Value1)*sum(Value2) aggregated by GroupName and Calculated dimension:

if(Value<=100,'Subgroup1',if(Value<=500,'Subgroup2','Subgroup3'))

If calculates it without Aggr the result for Group2,Subgroup2 = (500+400+150)*(10+20+10)=42 000

I need 500*10+400*20+150*10=14 500

[antoniotiman · Accepted solution]

[effinty2112]

GroupName	Subgroup	sum(Value*Value2)
		35000
Group1	Subgroup1	1000
Group1	Subgroup2	6000
Group2	Subgroup2	14500
Group2	Subgroup3	13500

Subgroup dim =

='Subgroup' & Match(-1,aggr(sum(Value),ID)<=100,aggr(sum(Value),ID)<=500,-1)

[maxsheva]

Sometimes hard things can be very easy

Thank you very much Antonio!