Accumulation in a Pivot Table

[#1]

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!

[Anonymous]

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

like

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)

[#3]

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?

[#4]

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, ...)...

[#5]

Hey,

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

Thanks

[Anonymous]

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