4 Replies Latest reply: Jun 19, 2012 7:35 AM by Michel Kreppold

# I need help with a multi year expression

Hi Community

What I like to do:

I like to build a pivot table with a expression about multi year.

For Example:

Actually Year:     Dez 2011+Jan 2012+Feb 2012 (that is no problem) vs

Last Year:          Dez 2010+Jan 2011+Feb 2011 (that ist my problem :-)

What I do:

I build a pivot table with 2 dimensions and a expression for the actually year like "Sum({\$<%DATUM={"<=\$(vDatenstand)"}>} ABSATZ_DIREKT)".

My current selection is:

Jahr_Monat_IST = 2011-Dez, 2012-Jan, 2012-Feb.

What I need is a idea for the "last year" expression. Can I get the selection and subtract 1 year?

I try different ways but nothing works.

Thanks
Michel

• ###### Re: I need help with a expression

Hi

I have used a variable (Variable1) to store your selection but you can use the field name :

=

(left(Variable1,4) - 1)

& Mid(Variable1,5,6)

& (Mid(Variable1,index(Variable1,',',1)+2,4)  - 1)

& Mid(Variable1,index(Variable1,',',1)+6,6)

& (Mid(Variable1,index(Variable1,',',2)+2,4) -1)

& Right(Variable1,4)

Not very good practice though...

• ###### Re: I need help with a multi year expression

Hi

Hm I try it but it doesn´t works.

Can I work with the calendar? I have 2 fields.

Jahr_Monat_IST and

Jahr_Monat_VJ

If I set my current selection, I see the field: Jahr_Monat_IST = 2011-Dez, 2012-Jan, 2012-Feb in green.

So I like try to use the white "selection" Jahr_Monat_VJ.

Maybe like this: sum({\$<Jahr_Monat_IST={"=\$(=Jahr_Monat_VJ)"}>} Absatz_Direkt)

• ###### Re: I need help with a multi year expression

See attached file. If you choose 2011-Dez, 2012-Jan, 2012-Feb in the GDate list box, you get the GQTY for 2010-Dez, 2011-Jan, 2011-Feb date.

If you choose 2011-Nov, 2011-Dez, 2012-Jan in the GDate list box, you get the GQTY for 2010-Nov, 2010-Dez, 2011-Jan date.

Hope this is what you need! See expression in straight table chart to understand.

giakoum

• ###### Re: I need help with a multi year expression

Great...... it works. Thanks