topic Re: Calculated collumn in a pivot table in QlikView

Calculated collumn in a pivot table

Mon, 27 Jan 2014 18:04:30 GMT

Hello,

In a pivot table in the dimensions I have company names, in the the rows i have the years. This table shows the sales evolution.

How can I add a collumn at the end of the years to display the ratio of the last two years?

Thank you in advance!

Re: Calculated collumn in a pivot table

Carlos_Reyes — Mon, 27 Jan 2014 18:12:28 GMT

If you have Years as columns (crossed field), then the only possibility I can think of (using the same chart and not creating a new one that is placed at the end of the first) is to use Dimensionality to define the expression that displays the ration of the last two years in the proper subtotal column. Check the QlikView help and play with that funtion in your table.

Your expression should be something like this:

If ( Dimensionality = X, Expression for the last tow years, Current Expression)

EDIT... Perhaps this little discussion can help you understand that function a little bit more:

Understanding the dimensionality function in qlikview

Re: Calculated collumn in a pivot table

Mon, 27 Jan 2014 18:31:37 GMT

Hello Carlos,

Didnt think of Dimensionality, will try it and get back to you!

Thanx a lot for extremely prompt reply!

Regards

On Jan 27, 2014 7:13 PM, "Carlos Alberto Reyes Díaz" <

Re: Re: Calculated collumn in a pivot table

Carlos_Reyes — Mon, 27 Jan 2014 18:37:13 GMT

No problem,

Here you have a small example.

Hope it's useful.

Re: Re: Calculated collumn in a pivot table

Tue, 28 Jan 2014 08:41:38 GMT

Your solution seems to be perfect!

The only problem im having is that the period dimension is a concat of two fields and one of them is a string.

Find attached an example application.

I have added the last column in a separate straight table.

Re: Re: Calculated collumn in a pivot table

Tue, 28 Jan 2014 14:03:50 GMT

Thanks again Carlos, its working, it's just that the partial sum option wasnt checked!

Regards

Re: Calculated collumn in a pivot table

Carlos_Reyes — Tue, 28 Jan 2014 15:11:42 GMT

Hi Hero,

I'm glad it worked for you. Only one little favor...

¿Can you mark the posts as answers and helpful so others can find faster responses?

Regards