topic Re: Roll Up in QlikView

Roll Up

brindlogcool — Tue, 26 Jun 2012 18:58:59 GMT

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

Re: Roll Up

Tue, 26 Jun 2012 19:15:11 GMT

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

Re: Roll Up

Tue, 26 Jun 2012 20:04:26 GMT

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

Re: Roll Up

brindlogcool — Tue, 26 Jun 2012 20:06:21 GMT

Hi Erica,

Thanks for your response.

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

Re: Roll Up

brindlogcool — Tue, 26 Jun 2012 20:10:04 GMT

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.

Re: Roll Up

giakoum — Tue, 26 Jun 2012 21:02:24 GMT

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

Re: Roll Up

brindlogcool — Thu, 28 Jun 2012 01:36:44 GMT

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

Re: Roll Up

Thu, 28 Jun 2012 13:18:29 GMT

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

Re: Roll Up

brindlogcool — Mon, 09 Jul 2012 02:10:07 GMT

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.

Re: Roll Up

Mon, 09 Jul 2012 07:27:47 GMT

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