topic Pivot table & Acumulation row in QlikView

Pivot table & Acumulation row

Thu, 04 Mar 2010 14:26:13 GMT

Hi everyone,I'm sure my issue is easy to solve..

I've a pivot table and I need to show in one of the columns a full accumulation of rows. Right now, those options are disabled.

How can I do to solve that?

Thxs in advance...

Pivot table & Acumulation row

wizardo — Thu, 04 Mar 2010 15:06:32 GMT

hi there,

well acutely this isn't so easy to solve:) especially since your using a pivot.

the problem with pivot is that it changes

when you make selection

or when you expand and collapse dimension

also you might have dimension spread out on the top (in one or more levels)

so it can get very tricky

there are few methods of manually doing accumulation.

one of them is the use of the functions rangesum() and above()

something like this rangesum(Num,above(Num,1,(3-1)))

the rangesum functions takes a coma seperated list of values and return the sum of them

the above functions in its extended format generates a comma seperated list of the values above it in a chart

together they can produce accumulation

like i said it works best in a st8 table (where it isn't acutely needed) but is kinda problematic ina pivot

attached is a sample QVW

in the sample it works on a pivot too because the pivot has no collapsing dimensions

but like i said when you start adding dimension it can get tricky

it can be solved (with the dimensionality() function and others) but its not simple (well as far as iv been trying)

hope this puts you on the right track

Mansyno

Pivot table & Acumulation row

wizardo — Thu, 04 Mar 2010 15:16:30 GMT

i tried attaching a file but seems there are problems again because it doesn't show

any it was a short sample so here is it's description

enter this line of code in the script.

it will produce a 10 rows of test data:

Load Chr(RecNo()+Ord('A')-1) as Alfa, RecNo() as Num autogenerate 10;

after reload put a st8 table on the sheet

add Alfa as dimension

add two expressions

on the first just put the field Num (just the field name without SUM )

on the second expression enter this :

rangesum(Num,above(Num,1,(3-1)))

this should give you the accumulation of Num on the 2nd expr

Mansyno

Pivot table & Acumulation row

Thu, 04 Mar 2010 15:47:46 GMT

Hi Mansyno, thxs for your answer!

I was reading about what I want to achieve in this foro and as you recommend me, I found about the above function.

I'll try to use it.. even though I've multiple dimensions 😞 and as you say (and now I understand) it get quite tricky...

I heard once that using the aggr function i could get the accumulation, what do u think about that??

Anyway, thxs for your well explained answer! xD and example!

Pivot table & Acumulation row

Thu, 04 Mar 2010 16:46:34 GMT

Mansyno,

I took the example you gave me: rangesum(Num,above(Num,1,(3-1))) and modify it a bit.

I needed the accumulation of all rows before an actual one. With your example I got (3-1) positions, so the only thing I changed was the number 3 for rowno().

That way, in the last row, would have the accumulation of all the ones before it.

rangesum(Num,above(Num,1,(rowno()-1)))

Pivot table & Acumulation row

Fri, 05 Mar 2010 09:47:22 GMT

Hi,

As a variant of this does anybody know how I can pick up the last non zero value in a dimension in a pivot table if the current value is null/zero. I realise that I can pick up the previous value using before and that I can offset before to pick up a prior one. The problem I have is that I cannot work out how far back the last non zero value was without creating a second expression that counts the number of consecutive null/zero values. In summary I have a solution to my issue but it requires 2 expressions. I would like to do it in one expression if possible.

Kind Regards,

Footsie