topic Re: Create a pie chart with different column values in QlikView

Create a pie chart with different column values

Tue, 10 Nov 2015 01:22:49 GMT

I have to create a pie chart with values present in 4 different columns of a straight table. How can I do it? What should be the dimensions and expressions (Also, what are they?). I am attaching an image that gives what values are to be used in the pie chart.

Re: Create a pie chart with different column values

rubenmarin — Tue, 10 Nov 2015 10:28:22 GMT

Hi, you can create the chart without dimensions and adding each column of the table as an expression that returns the % to show, ie:

for this expressions:

Returns this:

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 14:15:53 GMT

Here, you are manually defining the values. I need to add all the values present in each column and then use their totals as values for the pie chart. For example: Add all values in Column A and use the result as value in Pie Chart; similarly columns B,C and D.

Re: Create a pie chart with different column values

rubenmarin — Tue, 10 Nov 2015 14:39:21 GMT

It was an example of how you can do that, I don't know a way to use values from any chart as a source from another (maybe exporting and importing with a macro but you'll need a reload), so as a workaround you can replicate each of the table columns as expressions in the pie chart.

You'll need to adapt it using set analisys and/or Aggr() to simulate the dimensions. I will need more info about the table expressions to be more accurate.

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 14:45:24 GMT

You can try with Synthetic dimensions concept. Please post sample data qvw so community will help you better.

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 14:46:54 GMT

You can use Relative option.

Re: Create a pie chart with different column values

vincent_ardiet — Tue, 10 Nov 2015 15:00:06 GMT

Hi,
What contain your 4 columns? Do you have 4 expressions or the table is a cross table?

What are the expressions inside your table, and the dimensions?

Regards,

Vincent

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 15:20:16 GMT

I have four columns with Critical-High-Med-Low as numerical values. I need to create a pie chart with those values as percentages in the pie slices. Whenever I am adding the expressions as Critical, High, Medium and Low, the pie chart gives me all the values as 100% but not reflecting the original percentage of the values.

In simple terms, I need the total value of each column to appear in the Pie chart as percentage. I tried adding them as dimensions too, but that doesn't help either.

Re: Create a pie chart with different column values

rubenmarin — Tue, 10 Nov 2015 15:47:22 GMT

I need to know your expressions (at least two of them) to give more help building the expressions in the pie chart. Also the dimensions you're using in the table.

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 15:51:16 GMT

The Dimensions in the Straight table are few other fields (Both Text and Numerical Values) and the expressions contain fields that needed some calculations. The columns that I am talking about are taken as expressions.

Column Names: R, S, T, U

Dimensions are just columns from the flat file

Expressions in the Straight Table:

Sum(R)

Sum(S)

Sum(T)

Sum(U)

How to generate a pie chart with the values from the Columns R,S,T,U?

Re: Create a pie chart with different column values

vincent_ardiet — Tue, 10 Nov 2015 16:04:15 GMT

As I understand, the sum of Critical, High, Med and Low can be lower than 100?

In this case, you can add a 5th expression which is 100-sum(Critical)-sum(High)-sum(Low)-sum(Med)

So, you have an extra slice to keep the ratio constant and you can set a transparent color to make it disappear.

Re: Create a pie chart with different column values

IAMDV — Tue, 10 Nov 2015 16:06:54 GMT

Dheeraj - Why don't you use CrossTable and get them into two columns. Then you can use the Pie Chart as you need it. I think that works better because you don't have to manage new column in future.

Here's a simple video tutorial on how to use CrossTable along with Pros and Cons.

http://qlikshare.com/qlikview-video-tutorial-crosstable-qlikview-transpose-data/

Cheers,

Re: Create a pie chart with different column values

vincent_ardiet — Tue, 10 Nov 2015 16:25:20 GMT

Just create 5 expressions:

sum(R)

sum(S)

sum(T)

sum(U)

100-sum(R)-sum(S)-sum(T)-sum(U)

Re: Create a pie chart with different column values

rubenmarin — Tue, 10 Nov 2015 16:25:32 GMT

If the expressions are just a Sum() and you want the total we can ignore dimensions and create 4 expressions in the pie chart:

R: Sum(R)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

S: Sum(S)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

T: Sum(T)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

U: Sum(U)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

That returns the percentage of each over the total, maybe you can precalculate something in the script to improve performance (only if needed)

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 17:50:35 GMT

I can't use a cross table Deepak. That would another data table and my requirements wouldn't want that.

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 17:54:13 GMT

My question is quite straight.

These are the columns:

R S T U

1 1 1 0

0 1 1 0

1 0 0 0

1 1 0 1

------------------

3 3 2 1

I need the totals to be in the pie chart.

R: Sum(R)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

S: Sum(S)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

T: Sum(T)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

U: Sum(U)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

sum(R)

sum(S)

sum(T)

sum(U)

100-sum(R)-sum(S)-sum(T)-sum(U)

is not giving me the result.

Re: Create a pie chart with different column values

Tue, 10 Nov 2015 18:47:27 GMT

I was able to get the pie chart using the expressions

R: Sum(R)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

S: Sum(S)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

T: Sum(T)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

U: Sum(U)/(Sum(R) + Sum(S) + Sum(T) + Sum(U))

But when I select the pie, I don't see any change in the corresponding table values and vice-versa. It's like the pie chart is static and not dynamic. Any ideas what's wrong?

Re: Create a pie chart with different column values

Wed, 11 Nov 2015 23:07:18 GMT

I read that a Pie Chart can have only one dimension and only one expression. Is that true? Also, I tried adding 2nd dimension and the data is messed up.

I am attaching a new image that clearly shows what Fields I have and what I need in the pie chart. Please refer to it and advise accordingly.

Also, whenever I click on the pie slice, it should give me the respective field data in the table.

Thanks

Re: Create a pie chart with different column values

rubenmarin — Thu, 12 Nov 2015 08:03:19 GMT

That link redirects me to reduceimages.com/index.php?error=image_not_found

I'm not sure to understand your data so it's a good idea to have a look to that image or to have a sample which simulates the problem.