5 Replies Latest reply: Feb 1, 2015 1:05 PM by Elena Prandoni

# Accumulation in a Pivot Table

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

like

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

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

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

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

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