Dimension "difference" in a pivot table

[#1]

Good morning,

I have a pivot table which, for a selected month, compares the values of three expresions under the dimension Year. Something like this:

                    september

________________________________

          2011               |               2012

__________________|______________

e1 | e2 | e3 ..............| e1 | e2 | e3

I would like to add a new "Year" called "Diference 2012-2011" to expose the diference between the current year and the year-1 whatever it was nd for each expresion, resulting something like this:

                    september

___________________________________________________

          2011               |               2012  |               Diference

__________________________________________________

e1 | e2 | e3 ..............| e1 | e2 | e3........| e1 | e2 | e3

How can I do this?

Thanks in advance

[#2]

Hi,

This is just a work around but you can try it.

See attached file

Regards,

Alex

[#3]

In the 'Presentation' tag I see it appears a dimension or expresion whith no name but with 'Show partial sums' checked, I do not understand how you did so, supposing that this is the key of the question.

Nevertheless, if you checked 'Show partial sums' I do not undertand neither how you were able to get 98 and 91 instead of 70+168=238 and 159+250=409.

[christian77]

Here is how I do it:

expresión 1: sum({$<Año={'$(vAñoActual)'} >} Importe)

expresión 2: sum({$<Año={'$(vAñoAnterior)'} >} Importe)

diferencia: column(1)-column(2)

vAñoActual   =max(Año)

vAñoAnterior = max(Año)-1

It is necesary to place the equal sign in the Var pannel for this 2 variables.

Any year you select will give you the right comparison

Suerte Imenendez!!!

[#5 · Accepted solution]

Hi,

To explain my qvw sample.

On my Dimension tab, I used "Dimension" and "Year" Fields then I named the Year Field as "space"/"blank" so the label is hidden. that's the dimension you see in Presentation tab with no name. After that, I checked the "Show Partial Sum" of my "Year" Field (which as I said is the one with "space as name") Then put "Difference" in my "Label for Totals".

Then in my Expression tab I used SecondaryDimensionality() to locate the new Field (which is the SHOULD BE total for Year) and put  "sum({<Year= {$(=max(Year))}>}Data) - sum({<Year= {$(=max(Year)-1)}>}Data)" (which will minus my last year data to my current year data)

Just get back to me if that's what you need.

Hope that helps

Regards,

Alex

[#6]

There is one difference between my pivot table and your example which make that your solution do not work in my case, the difference is that I have the dimension 'month' over the dimension 'year', that is, I compare years for each month.

I tried your solution dropping month as a dimension and it works perfectly, unfortunately I can not drop 'month' in my real model.

If you are able to overcome this last obstacle...

[#7]

Hola,

I wrote the subtraction of this two expresions as a new one but the result were added as a new column under 2011 and under 2012. I need they appear under a 'year' called 'Variación' at the end of the table.

I think the problem is that I do not understand where you would write 'column(1)-column(2)'.

Gracias Christian

[christian77]

Hola:

That's the 3rd column. You can define an expresión by operating other expresións.

In a 3rd column, set

sum({$<Año={'$(vAñoActual)'} >} Importe) - sum({$<Año={'$(vAñoAnterior)'} >} Importe)

it is the same as column(1) - column(2) QV understand both. Plus you can use the name of the labels between brackets.

It´s clear that you are not going to use totals with this way.

If you wish to do it using dimensionality() and one of your dimensions is pivoted you must use also SecondaryDimensionality() and be prepare to work in detail. It´s a little tedious.

Please attach an example to your next post and I´ll show you some ways.

Alexis' way is good too, it is just another way.

[#9]

I d'ont understand what you mean with "That's the 3rd column".

I can define an expresion operating other expresions but the problem is that the new expresion appears under each value of year and I need it appears only in the summarize set.

Alexis way is usefull dropping the month and putting it as a selection out of the pivot table,

Gracias y saludos

[#10]

Hi,

You can just Add your Month Dimension before your Year.

and still do the same thing on your Year Field.

See attached