Roll Up

[brindlogcool]

Hi,

I have a table as below

Level	Amt
L1
L2
L3
L4
L5	1
L6	2
L6	7
L6	6
L6	5
L6	4
L6	3
L6	2

And i want it to roll up like below in the pivot table how to do it kind of running total but not sure.

Average of  L6 in L5, L4,L3,L2,L1 as one column

Sum of L6 in  L5,L4,L3,L2,L1 as one column

[#2]

Hi Brindlogcool

I'm not sure what you are requesting, but if you are after the average / sum of L6 in the values, no matter what the level, then these are the expressions you need:

=avg({<Level={"L6"}>} total Amt)

=sum({<Level={"L6"}>} total Amt)

Erica

[#3]

Hi brindlogcool,

You have to make an aggregation on ignoring Amt. field

aggr(sum({<Amt=>}Amt),Level).

hope the attachment helps you

Let me if issue still continues

thank  you

Meher

[brindlogcool]

Hi Erica,

Thanks for your response.

But if you use the above expression in pivot it is nto working.

[brindlogcool]

Hi Merwan,

Thans for your response. I am looking on the same row

Level	sum
L1	0
L2	0
L3	0
L4	0
L5	1
L6	29

I need the level L5,L4,L3,L2,L1 as 30.

[giakoum]

not sure what you want to achieve but please try sum(TOTAL {$<Level=>} [Amt])

[brindlogcool]

I am having data only at level 6 and Level 5 user want to see the aggregated level of Level 6 and Level 5 at the Level 4,3,2,1

[#8]

Taking simplified data as in your example:

Lev,

L1, 0

L2, 1

L2, 2

L3, 1

L3, 2

L3, 4

L3, 2

Calculated dimension (UpperLev): ='L'&´text((right(Lev,1)-1)

Expression: aggr(sum(Amt), Lev)

Outcome table:

UpperLev Sum

L0, 0

L1, 3

L2, 8

See attached example... Message was edited by: Christian Henke

[brindlogcool]

Thanks for your response Christian. i am looking at a different solution.

Considier the hierarchy L5,L4,L3,L2,L1. L5 is the lowest level and L1 is the higest.

Now the fact table will have only values for L5, L4 and remaining levels may or may not have values.

I want to display      

                              L1  value should be SUM(L5)+SUM(L4)+SUM(L3)+Sum(L2)+SUM(L1)

                              L2  value should be SUM(L5)+SUM(L4)+SUM(L3)+Sum(L2)

                              L3  value should be SUM(L5)+SUM(L4)+SUM(L3)

                              L4  value should be SUM(L5)+SUM(L4)

                              L5  value should be SUM(L5)

I would like to get this result.

[#10]

OK, got it...

Make sure list is ordered by level, than use formula: rangesum(below(sum(Amt),1,vAmtAggrLev))+Sum(Amt)

This is flexible and allows with the variable vAmtAggrLev to sum the number of levels below the current level