Today, one year ago in a Pivot Chart

[#1]

Hi Experts

I am trying to disply values in a Pivot Chart.

I have the following formula:

Sum ({<Auftragsdatum_aktJ={$(VDanteminus1Year)}>} ([Rohertrag_aktJ]))

It gives me the Sum of all earnings today, one year ago. BUT what I would like to retrieve is the sum of all earnings today, one year ago and earlier. Thus, < today, one year ago.

How would I have to script this?

Thank you for your help!

Jan

[Anonymous]

did you try

sum({Auftragsdatum_aktJ={'<=$(=$(VDanteminus1Year))'}>}([Rohertrag_aktJ]))

[#3]

Yes, I did. Once I use ' in front of the <=$ my variable is not recognised as such anymore...

[Anonymous]

to verify:

if you use your origin expression you get the value of today minus 1 year

and Auftragsdatum_aktJ contains dates previous than that date!

If you have an additional field for the actual year you might need to resolve this within set analysis

    MyYearField=,

if you use ' within set analysis twice you need to work with ' and " or even with ' and chr(39)

can you post a sample?

[#5]

Okay, maybe I will go a step back and explain what I am trying to do. I have a resident statement where I retrieve data of earnings from 2014 and 2015. In the Pivot I separate the earnings after year and a category of earning. This all works using:

Sum ({<Jahr={$(VaktJahr)}>} ([Rohertrag_aktJ])) --> for 2015

Sum ({<Jahr={$(VVJahr)}>} ([Rohertrag_aktJ])) > for 2014

But then I retrieve the whole year for 2014, which is not what I would like to do. I would like to display Year To Date for 2014.

Auftragsdatum_aktJ contains the order dates.

Rohertrag_aktJ contains the earnings 2014 and 2015

Maybe there is also a different solution to what I am trying to do since I do not seem to get this formula working that works with <>. It only semms to work with =...

Thanks for your intensive help, Rudolf.

[Anonymous]

Real strange, but what I can confirm is that it works with ">=" or "<=". I use it very often including previous year calculation uptodate (exactly what you want to achieve)

would the combination of both work?

Sum ({<Jahr={$(VVJahr)},Auftragsdatum_aktJ={'<=$(=$(VDanteminus1Year))'>} ([Rohertrag_aktJ]))

did you try your expression in a textbox and play with different combinations?

[#7]

Because of the ' my VDateminusoneYear is not grey, thus not recognised as a variable.

Whithout the ' the last part does not work...

But the hint with the text field is good. I will play around a bit.

[Anonymous]

there is an ending  } missing after Auftragsdatum_aktJ and before >

[#9]

The last formula? Sorry, I do not see it...

[Anonymous]

= sum(............., Auftragsdatum_aktJ={<=$(=$(vDanteminus1Year))}>} ([Rohertrag_aktJ]))

                                                                                                  I