topic Re: Get dimension values in Pivot Chart Expression in QlikView

Get dimension values in Pivot Chart Expression

Wed, 25 Nov 2020 16:16:04 GMT

I have a dataset with two dimensions

Dimension 1 = A and It has values A1,A2, A3

Dimension 2 = B and it has values B1, B2

If I do a pivot chart using those two dimensions I would get something like this

	B1	B2
A1
A2
A3

In expression of that Pivot Chart, I want to refer to the Column Headers and Row Headers so that I can get something like this

	B1	B2
A1	A1B1	A1B2
A2	A2B1	A2B2
A3	A3B1	A3B2

Can you suggest how to refer to the column header in the expression?

Re: Get dimension values in Pivot Chart Expression

vishsaggi — Thu, 09 Feb 2017 17:20:24 GMT

Add Pivot table and

Dimensions

Expression as

= A&B

Move your Dimension B on top of the expression.

Re: Get dimension values in Pivot Chart Expression

Thu, 09 Feb 2017 17:27:58 GMT

I appreciate your quick response.

What if I do pivot on same dimension e.g A? In this case how will A&A won't work. Is there a way to distinguish between first A and second A

A1 A2 A3

Re: Get dimension values in Pivot Chart Expression

vishsaggi — Thu, 09 Feb 2017 17:36:18 GMT

I quite did not get you what you mean by A & A. One of the values in B is empty so it is displaying the Avalues with empty space.

What is that you are trying to achieve? Can you elaborate?

Re: Get dimension values in Pivot Chart Expression

Thu, 09 Feb 2017 17:48:57 GMT

the ultimate goal is to build a statistical model for correlation between different values of the same dimension.

Correl(A1, A2)

Correl (A1, A3).. etc

for that purpose, I am thinking of using the same dimension - A twice in the Pivot Chart.

A1` A2` A3`

then in expression part, I want to refer to the column header and Row header of that intersection and then calculate the correlation.

A1` A2` A3`

A1 Correl(A1, A1`) Correl(A1, A2`)

Re: Get dimension values in Pivot Chart Expression

albertovarela — Thu, 09 Feb 2017 17:52:06 GMT

By design you cannot have same dimension twice on the same chart (it can be done but you have to force it via calculated dimension)

Re: Get dimension values in Pivot Chart Expression

vishsaggi — Thu, 09 Feb 2017 17:55:32 GMT

Click on Calculated dimension as Alberto suggested if you want to add same dimension twice. Like

= A

Re: Get dimension values in Pivot Chart Expression

Thu, 09 Feb 2017 18:00:20 GMT

I have already done that..

My question is - how can you refer to the values of the first A and second A in the expression of Pivot Chart

If I use Vishwarath's suggestion of adding Expression = A&A

it doesn't work.

Re: Get dimension values in Pivot Chart Expression

vishsaggi — Thu, 09 Feb 2017 18:24:07 GMT

Just label them differently. What is your expected output to be ?

Re: Get dimension values in Pivot Chart Expression

sunny_talwar — Thu, 09 Feb 2017 18:32:23 GMT

Can you share an example where you use actual numbers instead of text?

Re: Get dimension values in Pivot Chart Expression

Thu, 09 Feb 2017 18:38:49 GMT

A1 A2 A3

A1 A1A1 A1A2 A1A3

A2 A2A1 A2A2 A2,A3

A3 A3A1 A3A2 A3A3

Re: Get dimension values in Pivot Chart Expression

Thu, 09 Feb 2017 19:57:28 GMT

I don't want numbers. I want text only.

I want to display value of column header and row header in each intersection

Re: Get dimension values in Pivot Chart Expression

Fri, 10 Feb 2017 16:05:08 GMT

I was able to solve this.. But i have more questions..

How do I upload my qlikview file so that I show you where I am facing issues.

Re: Get dimension values in Pivot Chart Expression

vishsaggi — Fri, 10 Feb 2017 16:07:07 GMT

When you click on reply, you can see a link on top right corner of that reply window that says "use advanced editor" once you click on that you can use an attach icon in bottom right corner for uploads!!!

Re: Get dimension values in Pivot Chart Expression

Fri, 10 Feb 2017 16:30:41 GMT

I don't see that option. Is it browser specific?

Re: Get dimension values in Pivot Chart Expression

albertovarela — Fri, 10 Feb 2017 16:43:38 GMT

Re: Get dimension values in Pivot Chart Expression

Fri, 10 Feb 2017 17:36:46 GMT

@stalwar1 @vishsaggiPlease see attached

Re: Get dimension values in Pivot Chart Expression

sunny_talwar — Sat, 11 Feb 2017 00:08:41 GMT

What is the expected output for this sample?

Re: Get dimension values in Pivot Chart Expression

Sat, 11 Feb 2017 14:39:57 GMT

@Sunny T Expected output is to see expression calculated for each row and column intersection. similar to Pivot 1

Re: Get dimension values in Pivot Chart Expression

sunny_talwar — Sun, 12 Feb 2017 00:31:12 GMT

The problem is that set analysis is evaluated once per chart and what you need is to use Aggr() function somehow to get what you want. But again, I am not sure I know what the final table will look like in respect to numbers to offer a better suggestion here.