9 Replies Latest reply: Feb 20, 2015 3:43 AM by Rudolf Linder

# Today, one year ago in a Pivot Chart

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?

Jan

• ###### Re: Today, one year ago in a Pivot Chart

did you try

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

• ###### Re: Today, one year ago in a Pivot Chart

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

• ###### Re: Today, one year ago in a Pivot Chart

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?

• ###### Re: Today, one year ago in a Pivot Chart

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.

• ###### Re: Today, one year ago in a Pivot Chart

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?

• ###### Re: Today, one year ago in a Pivot Chart

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.

• ###### Re: Today, one year ago in a Pivot Chart

there is an ending  } missing after Auftragsdatum_aktJ and before >

• ###### Re: Today, one year ago in a Pivot Chart

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

• ###### Re: Today, one year ago in a Pivot Chart

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

I