topic Re: Advanced Aggregation in QlikView

Advanced Aggregation

tedalien — Mon, 03 Nov 2014 16:03:32 GMT

Hi dear Gurus,

I hope is ok I ask a question. I am trying to solve a problem and I think the best option would be the Aggr function.

In principle I am trying to achieve getting the sums of an higher level of aggregation (Level Dim1+Dim2) to be displayed in each raws of the subsequent level.

Dm1 and Dim3 are limited in number of unique elements, but the Dim2 can be many.

Dim1	Dim2	Dim3	Value Split	I need
AAA	AA	A	5.6	6.9
AAA	AA	B	1.3	6.9
AAA	BB	A	1.8	2.3
AAA	BB	B	0.5	2.3
AAA	CC	O	1.5	1.5
BBB	BB	A	3	16.6
BBB	BB	B	12.1	16.6
BBB	BB	O	1.5	16.6

I tried with:

sum(Total(aggr(aggr(sum(Value Split),dim1,dim2),dim3)) and this works as long as there is one Dim2 selected, when I release the selection the Total is calculated on all Dim1 and Dim2 elements.

I tried some variation but so far no success.

Can you suggest if this is the way (best) or if there is antoher?

In case this is the way, can you help me fixing the set analysis?

Many many Thanks!!

Alen

Re: Advanced Aggregation

MK_QSL — Mon, 03 Nov 2014 16:37:04 GMT

You can achieve this by two different ways....

1) SCRIPT SIDE

=================================================

Temp:

Load * Inline

[

Dim1, Dim2, Dim3, Value Split

AAA, AA, A, 5.6

AAA, AA, B, 1.3

AAA, BB, A, 1.8

AAA, BB, B, 0.5

AAA, CC, O, 1.5

BBB, BB, A, 3

BBB, BB, B, 12.1

BBB, BB, O, 1.5

];

Join

Load

Dim1,

Dim2,

SUM([Value Split]) as Total

Resident Temp

Group By Dim1, Dim2;

====================================

2) Front End (UI SIDE)

Create a Straight Table

Dimension

Dim1

Dim2

Expression

SUM([Value Split])

SUM(TOTAL <Dim1, Dim2>[Value Split])

Re: Advanced Aggregation

satishkurra — Mon, 03 Nov 2014 16:38:23 GMT

Write the expression like below

sum([Value Split])/Sum(TOTAL <Dim1,Dim2,Dim3> [Value Split])

The above expression ignores all the dimensions in your source data except DIm1, Dim2 and Dim3

Thanks

Satish

Re: Advanced Aggregation

maxgro — Mon, 03 Nov 2014 19:45:38 GMT

with Total and angle brackets you can define the level of calculation

The calculation will be made disregarding all chart dimension except those listed

sum(TOTAL [Value Split])

sum(TOTAL <Dim1> [Value Split])

sum(TOTAL <Dim2> [Value Split])

sum(TOTAL <Dim1,Dim2> [Value Split])

......

Re: Advanced Aggregation

tedalien — Tue, 04 Nov 2014 08:26:33 GMT

Dears Manish, Statish and Massimo ,

thanks for your reply. Indeed your solution returns a sum on the level indicated in the set analysis, but it does not seem clustering it.

What I am trying to achieve is that the sum will be done on each combination of Dim1,Dim2. The value split of the Dm3 comes as disagregation of the other two. The Solutiom of Statish seems close to what I need but the Total seems calculated with all values of each dimesnion and not the one associated to Dim1 and Dim2. Should I include the $somewhere or am I totally out of track?

The Script solution with INLINE is not an apotion because there are too many elements. Dim1 has about 7 different entries, Dm3 has 5 but Dmi2 has 65.

Your effort and participiation is highly appreciated!

Many Thanks,

Alen

Re: Advanced Aggregation

MK_QSL — Tue, 04 Nov 2014 08:31:06 GMT

Can you provide the Result you are looking for... We have given the solution as per your expected result...

NOTE : Check our solution with your question where you mentioned ... I need

Re: Advanced Aggregation

jagan — Tue, 04 Nov 2014 08:41:11 GMT

Hi,

Try this

For Value Split - sum(Value Split)

Result you Need - Sum(TOTAL <Dim1, Dim2> [Value Split])

Hope this helps you.

Regards,

jagan.

Re: Advanced Aggregation

tedalien — Tue, 04 Nov 2014 11:48:53 GMT

Hi Jagan,

if I try this solution I get as below

Dim1	Dim2	Dim3	Value Split	I need
AAA	AA	A	5.6	27.3
AAA	AA	B	1.3	27.3
AAA	BB	A	1.8	27.3
AAA	BB	B	0.5	27.3
AAA	CC	O	1.5	27.3
BBB	BB	A	3	27.3
BBB	BB	B	12.1	27.3
BBB	BB	O	1.5	27.3

Tot 27.3

I need to keep the cluster as below:

AAA	AA	A	5.6	6.9
AAA	AA	B	1.3	6.9

AAA	BB	A	1.8	2.3
AAA	BB	B	0.5	2.3

AAA

1.5

BBB	BB	A	3	16.6
BBB	BB	B	12.1	16.6
BBB	BB	O	1.5	16.6

Your solution would wotk only when I select both Dim1 and Dim2

Regards,

Alen

Re: Advanced Aggregation

tedalien — Tue, 04 Nov 2014 11:51:42 GMT

Hi Manish,

I think you understood correctly as the column "I Need" is the result I want to achieve.

For some reason it seems that the above solutions did not work.

Maybe I am misunderstanding or there other reasons in my data which prevent me to see this result.

I will try what you mentioned in a small application and see if it makes the difference.

Thank very much you so far

Alen

Re: Advanced Aggregation

jagan — Tue, 04 Nov 2014 12:31:33 GMT

Hi,

Try like this if all this fields in Single table then this definitely works

Pivot Table:

Dimension: Dim1, Dim2, Dim3

For Value Split - sum(Value Split)

Result you Need - Sum(TOTAL <Dim1, Dim2> [Value Split])

Regards,

Jagan.

Re: Advanced Aggregation

tedalien — Tue, 04 Nov 2014 13:07:20 GMT

Jagan and ALL,

Indeed it does when I load the data as INLINE.

I was misunderstanding your instructions and thought you where suggesting the <dim1,dim2> to be applied to the set analysis not to the TOTAL.

I got itnow and applied to my application with a more complex form seems also working.

More test ti be dine but THANK YOU!

It has been enlightening!

Regards,

Alen