topic Re: Accumulation in a Pivot Table in QlikView

Accumulation in a Pivot Table

Fri, 30 Jan 2015 12:36:07 GMT

Hi,

i have following Problem. I build a Pivot table and i want a column which makes a sum about the values but with a Special function!

There must be a start Point, it´s the first Month in the year (January), and there must be a end Point which the user can choose. My Expression is like this:

=sum({<Monat = {"$(= '>=' & 'Jan' & '<='getselelctedvalue(monat))"}>} Werte)

But it doesn´t work. Is there any solution?

I need only a range between the fix start month and the flexible endpoint...

I hope somebody can help me. Thanks!

Re: Accumulation in a Pivot Table

Anonymous — Fri, 30 Jan 2015 13:47:57 GMT

First load your month with dual or you can do a in line load

LOAD * INLINE [

Monat,Month

Jan,1

Feb,2

Mar,3

Dec,12

];

so that you can get numeric value for comparison

Then use

=sum({<Month={'>=1'},Month={'<=$(var1)'}>}value)

PS create a variable with

var1=GetFieldSelections(Month)

Re: Accumulation in a Pivot Table

Fri, 30 Jan 2015 14:48:16 GMT

Ok thanks. Thiis is a good solution. My Problem is that i have to selection on Monat (Jan, Feb, ...). Is there a solution to make this based on Monat?

Re: Accumulation in a Pivot Table

Fri, 30 Jan 2015 14:57:47 GMT

On my Dashboard der is a numeric value for the only month´s too (jan,1; feb, 2 ...) but the seletion will happend about the Monat´s Names (Jan, Feb, ...)...

Re: Accumulation in a Pivot Table

Sat, 31 Jan 2015 12:19:37 GMT

Hey,

is there really no possibility to solve that problem? A sum about an range between several month´s?

Thanks

Re: Accumulation in a Pivot Table

Anonymous — Sun, 01 Feb 2015 18:04:54 GMT

Hi Thomas,

yuo can use the variable

vEnd = Concat({<Monat={"$(=GetFieldSelections(Monat))"} >}Month)

and the expression: =sum({1<Month={'>=1<=$(vEnd)'}>}value)

where Month is the numeric field chandel suggested.

Rember that "1" before <, makes the expression independent from selections (but it works for selection on Monat because it's related to Month)... so if you need that this expression is changed by selection on OtherField you must add a set analysis with p() function (which ignores "1") as:

=sum({1<Month={'>=1<=$(vEnd)'}, OtherField = p(OtherField)>}value)

Let me know if it can be ok for you!

Elena