topic Simple bar chart of 2 variables in QlikView

Simple bar chart of 2 variables

Wed, 07 Dec 2011 08:16:46 GMT

Hey,

I'm totally knew to Qlikview and I want to make a barchart of ProductID and UnitsInStock.

I have a table, called products that has a column ProductID and a column UnitsInStock, so I just want those 2 on a bar chart.

But how do I do this? Cause if I choose ProductID as first dimension and as expression UnitsInStock, it doesn't work.

Do I always have to use count, sum or something else in the expression?

Thanks in advance!

Simple bar chart of 2 variables

Wed, 07 Dec 2011 10:28:56 GMT

Hi,

You don't have to use count or sum. Just take ProductID as Dimension and UnitsInStock as Expression like this ( =UnitsInStock ) in Bar chart. It will automatically show you the no. of units in stock per product id.

Apurva

Simple bar chart of 2 variables

richard_chilvers — Wed, 07 Dec 2011 10:39:39 GMT

I think that approach is fine as long as there is just one record for each ProductID. However, if ProductID is duplicated in anyway (for example an additional field per record shows StockLocation) then this will show no value/Null value. Its best to use Sum which works in both cases.

Simple bar chart of 2 variables

Wed, 07 Dec 2011 11:55:40 GMT

I think your values are getting repeated in the database,

so if you simply apply units in stock then it won't work.

So there must be a date associated with each value,

so that you can find units in stock of product id accoding to last entry in the date.

eg:

product id units in stock date

1 10 12/04/2011

1 8 12/05/2011

then you can find out units in stock of product id, which is 8 not 10 according to last date entry.

So try to associate date with each data entry then apply the below expression in your bar chart:

=aggr(aggr(units in stock,product id),aggr(product id,max(date)))

I think it will help.

regards

vijit

Simple bar chart of 2 variables

Wed, 07 Dec 2011 12:20:42 GMT

That doesn't work, i don't understand why but it gives an error.

Simple bar chart of 2 variables

Wed, 07 Dec 2011 12:21:19 GMT

We don't work with dates, it's just the stock at this moment that's in the table

Simple bar chart of 2 variables

richard_chilvers — Wed, 07 Dec 2011 12:22:49 GMT

Are you able to send the application for us to look at ?

Or ... what error are you seeing ?

Simple bar chart of 2 variables

Wed, 07 Dec 2011 12:26:02 GMT

It says 'error in the expression' when i type (=UnitsInStock) in the expression field

But for every productID there's only one value for UnitsInStock and every productID is mentioned only once in our table.

Simple bar chart of 2 variables

Wed, 07 Dec 2011 12:26:11 GMT

is there any primary key according to which you can differentiate your data other than product id , as it is getting repeated.

Simple bar chart of 2 variables

Wed, 07 Dec 2011 12:34:29 GMT

Every productID is only mentioned once in our table Products, so that's the key.

Productname can also be the key, but that's not a good idea, i think.

But in other tables in our database productID is used to, can that be the problem?

Simple bar chart of 2 variables

Wed, 07 Dec 2011 13:28:13 GMT

The problem is indeed that ProductID is used in other tables in our database.

I tried an application with only the table products and than it works with dimension=productID and expression: UnitsInStock

Isn't it possible to only use the ProductID of the table products, like products.productID in sql.

Simple bar chart of 2 variables

richard_chilvers — Wed, 07 Dec 2011 13:52:40 GMT

Yes, it is possible.

But it may be that you need to check the structure of your data is in good order, which may well sort out the problem.

If you use Ctrl T to show your data, make sure there are no unsual relationships going on.

Simple bar chart of 2 variables

Wed, 07 Dec 2011 14:01:05 GMT

So products.productID should work?

I don't think there are unusual relationships

Simple bar chart of 2 variables

richard_chilvers — Wed, 07 Dec 2011 14:09:15 GMT

Did you try Ctrl T ?

You can QUALIFY the field productID in the script which means it is no longer linked to other tables. (Take a look at the online help).

You should then be able to do what you suggest. When you list the fields in your chart object, you will see products.productID is now listed. But it is not a good solution and could easily affect other objects in your application.