I need help with a multi year expression

[#1]

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

[giakoum]

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

[#3]

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)

[giakoum · Accepted solution]

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

[#5]

Great...... it works. Thanks