topic Re: Creating a bar chart with 2 dimensions and 2 expressions in QlikView

Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 14:18:53 GMT

Hello everyone,

I am new to QlikView and i am trying to create a bar chart with two dimensions from two different tables, and both dimensions has the same values. I also create two expressions to count the total of each one.

The chart is presenting the correct values, however it displays the same value in each line, as you can see in the print screen below:

When i create the chart using only one dimension and one expression, the chart is displayed correctly, as shown in the print screen below:

What i have to do to show both dimensions in one single line as presented in the chart above??

Thanks in advance,

Rodrigo

Re: Creating a bar chart with 2 dimensions and 2 expressions

MK_QSL — Tue, 15 Sep 2015 14:25:05 GMT

Provide sample QVW !

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 14:28:51 GMT

if both dimesnions have the same value, can you not make that field a key and then you would only have 1 diemnsion and expression but get data from both tables.

Anotehr thought - can your single expression be (Expression 1 Expression 2) ? If both dimesnisons have the same value, can you pick one of them and have your expression be the total of the 2 (not sure if you are doing sum, count but it should not mater)

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 16:52:54 GMT

Hi Adam,

Thanks for your tips!!

I tryed to make that field a key, but is not possible to execute my script when i use the same name for both dimensions. I received an error message saying that is not possible to execute the script.

I need to display both dimenisions separately, the user should see both information in 2 distinct colors. When i said the dimension have the same value, i meant that both dimension displays values of "less tanh 1 year", "more than 10 years", "1 to 2 years", etc.

Thanks,

Rodrigo

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:03:20 GMT

I do not understand why you cannot make them a key - QlikView allows that and that is how tables get linked together - 1 and only 1 field in 2 tables with the same name - if you have multiple fields of the same name pointing from one table to another, you will get a synthetic key (which is not good) but the script will run (I have run into the synthetic key a time or 2).

What about a single dimension (pick one) and have your 2 expressions, and have the bars set as stacked?

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:15:50 GMT

Creating a single dimension and having 2 expressions displays the chart the way i need, but now the values in red color are wrong, all are with the same value, as shown below:

I am counting the total of the two dimensions in 2 expressions:

Count (expression1)

Count (expression2)

Only the first expression bring me the correct values.

Do you know why this happened?

Thanks,

Rodrigo

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:15:59 GMT

what about crating the same new dimension when you load both tables - something like below

LOAD *,
If([ Days]>= 0 and [ Days] < 31,'0-30',
if ([ Days] > 31 and [ Days] < 46,'31-45',
if ([Days] > 45,'Over 45'))) as Age;

or possibly a calculated dimension with your 2 expressions represented as a stacked bar.

Har to determine what is best, if any of these suggestions without seeing the data model

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:19:37 GMT

is the value of 79 in red) correct for any of the dimesnion values?

any chance you can provide a qvw with the data?

not being able to name the 2 dimesnions the same does not make sense

Re: Creating a bar chart with 2 dimensions and 2 expressions

Colin-Albert — Tue, 15 Sep 2015 17:23:05 GMT

Try a trellis chart if you need 2 dimensions & 2 expressions.

Create bar chart with 2 dimensions & 2 expressions and tick the trellis option on the Dimensions tab.

The Year bucket, should be your second dimension.

Otherwise just use 1 dimension & 2 expressions.

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:26:52 GMT

I am thinking the issue is because the expresssion 2 does not really go with the dimension

If you could send a qvw that would be a great help

do you want your bars grouped (like in the example above), stcked like some earlier ones, or don't care as long as the numbers are correct - stacked to me only makes sense the different colors/dimensions add to a meaningful total

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:34:32 GMT

No, the value of 79 in red is incorrect for any of the dimensions.

I have created a chart with the correct values for this second dimension:

There is no value 79...

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:36:53 GMT

i see but the total of all of the bars adds up to 79 (4 + 4 + 8 + 11 + 20 + 32)

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:37:44 GMT

Unfortunately my project can´t be shared, i will try to make a new qvw with only this chart, without the others i have in this project.

I want the bars grouped (like in the example above) and doesn´t metter if they are stacked or not.

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:38:45 GMT

I am still thinking you need to get to 1 dimension

any chance of gett a qvw?

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:39:31 GMT

Excatly... But why it shows the sum of the bars??

I just create a expression of count.

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 17:43:08 GMT

When i change the dimension i choose to use in the chart for the second dimension, it shows the correct values, otherwise the other dimension shows the sum again:

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 19:01:55 GMT

Hi Adam,

Follow attached the qvw.

Hope you can help me.

Thanks,

Rodrigo

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 19:14:38 GMT

my guess is that you cannot name them the same, at least in what you sent becasue those are the only fields in the table.

I would suggest coming up with a common name for the dimension and load both fiels as that name - I would also include other fields from the tables as well. I cannot verify as I do not have the data and cannot perform a reload - sorry

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 19:26:40 GMT

I just ignore the others fields in this example, but in the original project the other fields are not ignored.

Anyway, those fields cannot have a common name for the dimension, because i should show the 2 dimensions separetely.

Re: Creating a bar chart with 2 dimensions and 2 expressions

Tue, 15 Sep 2015 19:30:01 GMT

you can have 1 dimension with 2 expressions and the expressions is what would make them unique with the red and blue bar