topic Dimension "difference" in a pivot table in QlikView

Dimension "difference" in a pivot table

Thu, 04 Oct 2012 09:48:05 GMT

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

Re: Dimension "difference" in a pivot table

Thu, 04 Oct 2012 10:22:32 GMT

Hi,

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

See attached file

Regards,

Alex

Re: Dimension "difference" in a pivot table

Thu, 04 Oct 2012 10:55:15 GMT

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.

Re: Dimension "difference" in a pivot table

christian77 — Thu, 04 Oct 2012 12:13:53 GMT

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

Re: Dimension "difference" in a pivot table

Thu, 04 Oct 2012 12:49:19 GMT

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

Re: Dimension "difference" in a pivot table

Thu, 04 Oct 2012 15:48:34 GMT

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

Re: Dimension "difference" in a pivot table

Thu, 04 Oct 2012 16:00:29 GMT

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

Re: Dimension "difference" in a pivot table

christian77 — Thu, 04 Oct 2012 16:14:54 GMT

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.

Re: Dimension "difference" in a pivot table

Fri, 05 Oct 2012 07:45:21 GMT

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

Re: Dimension "difference" in a pivot table

Fri, 05 Oct 2012 08:49:05 GMT

Hi,

You can just Add your Month Dimension before your Year.

and still do the same thing on your Year Field.

See attached