Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
giakoum
Partner - Master II
Partner - Master II

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

View solution in original post

4 Replies
giakoum
Partner - Master II
Partner - Master II

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

Not applicable
Author

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
Partner - Master II
Partner - Master II

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

Not applicable
Author

Great...... it works. Thanks