topic Re: Aggr by calculated field in QlikView

Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 11:09:09 GMT

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

)

Re: Aggr by calculated field

shraddha_g — Fri, 08 Sep 2017 11:22:36 GMT

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

)

Re: Aggr by calculated field

its_anandrjs — Fri, 08 Sep 2017 11:25:05 GMT

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)

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 11:34:57 GMT

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?

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 11:39:24 GMT

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

)

Re: Aggr by calculated field

its_anandrjs — Fri, 08 Sep 2017 11:47:58 GMT

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

Re: Aggr by calculated field

Kushal_Chawda — Fri, 08 Sep 2017 11:51:32 GMT

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

Re: Aggr by calculated field

effinty2112 — Fri, 08 Sep 2017 13:03:23 GMT

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

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 13:16:29 GMT

Output is

Group1 | 1 | 100

Group1 | 2 | 300

Group2 | 2 | 650

Gruop2 | 3 | 900

The same as Andrew's

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 13:20:09 GMT

But if this calculated dimension is not integer?

It is string value.

Re: Aggr by calculated field

effinty2112 — Fri, 08 Sep 2017 13:25:54 GMT

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

Re: Aggr by calculated field

antoniotiman — Fri, 08 Sep 2017 13:28:36 GMT

Like this ?

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 14:02:55 GMT

Would you please write an expression?

Re: Aggr by calculated field

antoniotiman — Fri, 08 Sep 2017 14:10:26 GMT

As You see it

Pivot Table :

1 Dimension

1 Calculated Dimension

1 Expression

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 14:14:46 GMT

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

Re: Aggr by calculated field

antoniotiman — Fri, 08 Sep 2017 14:18:10 GMT

Straight Table

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 14:55:36 GMT

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

Re: Aggr by calculated field

antoniotiman — Fri, 08 Sep 2017 15:06:04 GMT

Re: Aggr by calculated field

effinty2112 — Fri, 08 Sep 2017 15:17:22 GMT

GroupName	Subgroup	*sum(ValueValue2)**
		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)

Re: Aggr by calculated field

maxsheva — Fri, 08 Sep 2017 15:27:55 GMT

Sometimes hard things can be very easy

Thank you very much Antonio!