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

)

[shraddha_g]

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

)

[its_anandrjs]

You can try to create the calculated dimension, Is this you looking

LOAD *,

if(Value <= 100,1,

if(Value <= 500,2,3)) as Flag;

LOAD * Inline [

GroupName, Value

Group1, 100

Group1, 300

Group2, 500

Group2, 900

Group2, 150

];

And on UI

Dim:- GroupName, Flag

Expression:- =aggr(sum(Value),GroupName,Flag)

[maxsheva]

Hi Anand,

Thanks for your solution. It is correct according to the performance and implementation.

Unfortunately I can't create flag in script according to the real data model.

I tried before but correct result is only on the fly calculation.

Can I do this kind of aggregation on the front-end side?

[maxsheva]

Hi Shraddha,

As the result I need aggregated sum of values instead subgroup like in your expression.
Sorry for my data example. The second calculated dimension should be built on the different value.

Like this

Expression is

Aggr(Sum(Value1),GroupName

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

)

[its_anandrjs]

Its a single flag why not to create this in the Data model and refresh the data.

[Kushal_Chawda]

What is the expected output for the data which you shared? Can you give one example how actually calculation need to happen?

[effinty2112]

Hi Max,

I added an ID field to your data:

Data:

LOAD *, RecNo() as ID Inline [

GroupName, Value

Group1, 100

Group1, 300

Group2, 500

Group2, 900

Group2, 150

];

Now we can make this straight table:

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

New Dim is the calculated dimension:

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

Regards

Andrew

[maxsheva]

Output is

Group1 | 1 | 100

Group1 | 2 | 300

Group2 | 2 | 650

Gruop2 | 3 | 900

The same as Andrew's

[maxsheva]

But if this calculated dimension is not integer?

It is string value.