Advanced Aggregation

[tedalien]

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

[MK_QSL]

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

[satishkurra]

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

[maxgro]

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

......

[tedalien]

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

[MK_QSL]

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

[jagan]

Hi,

Try this

For Value Split - sum(Value Split)

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

Hope this helps you.

Regards,

jagan.

[tedalien]

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

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

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

Regards,

Alen

[tedalien]

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

[jagan · Accepted solution]

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.