topic Re: Help plotting bar chart when an item is appears in two bars in QlikView

Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 09:25:20 GMT

Hi I'm new to QlickView and was looking for some help.

I have two columns of data, black_or_white and cat_or_dog.

Header 1	Header 2	Header 3
Animal_ID	black_or_white	cat_or_dog
Animal_1	black	dog
Animal_2	white	dog
Animal_3	black	cat

On the same bar chart id like to plot a bar for the amount of dogs, a bar for the amount of black dogs, a bar for the amount of cats, and a bar for the amount of black cats.

I have managed to plot the amount of cats v dogs by having animals at the dimension and count(animals) as the expression. I have also managed to plot amount of white dogs, amount of black dogs, amount of white cats, amount of black cats using:

if(cat_or_dog = cat, if(black_or_white = white, 'white cat', 'black cat'), if(black_or_white = white, 'white dog', 'black dog') as the dimension.

But neither of these things is what i want. i want a bar for amount of dogs, and a bar for amount of black dogs....

Thanks very much

Re: Help plotting bar chart when an item is appears in two bars

MK_QSL — Thu, 13 Aug 2015 09:32:04 GMT

Very confusing... I think you have to use Total Qualifier (may be)...!

Better if you can provide the result you are looking for based on sample table given.

Re: Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 09:43:35 GMT

Hi thanks a lot for the reply. for the above example id like dogs = 2, black dogs = 1, cats = 1, black cats = 1.

Re: Help plotting bar chart when an item is appears in two bars

MK_QSL — Thu, 13 Aug 2015 09:57:10 GMT

Like this?

Re: Help plotting bar chart when an item is appears in two bars

kavita25 — Thu, 13 Aug 2015 10:09:34 GMT

Check the qvw.

Re: Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 10:16:09 GMT

Yes! that will do. thanks very much for your help

Re: Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 11:00:36 GMT

Hey Manish thanks for your help.I'm nearly there.

Can you explain to me whats going on with the:

COUNT({<something = {'something'}>}DISTINCT [ID]) notation.

basically I simplified my data to try and explain it. What i want is instead of checking if a dog is black i want to check if the field is null. but i cant work out how to get the IsNull(field) to work in the expression you gave me.

Thanks a lot

Re: Help plotting bar chart when an item is appears in two bars

MK_QSL — Thu, 13 Aug 2015 11:11:24 GMT

COUNT({<something = {'something'}>}DISTINCT [ID])

COUNT(DISTINCT ID) will count all Distinct ID

{<something = {'something'}>} This is set analysis which will work as a filter.

{<Month = {'March'}>} will give you result where month = March...

Say... SUM(If(Month = 'March',Sales)) = SUM({<Month = {'March'}>}Sales)

For Null

COUNT({<cat_or_dog = {'dog'}, black_or_white = {'=Len(trim(black_or_white))=0'}>}DISTINCT Animal_ID)

Re: Help plotting bar chart when an item is appears in two bars

MK_QSL — Thu, 13 Aug 2015 11:15:36 GMT

Or you can use below

COUNT({<cat_or_dog = {'dog'}>}DISTINCT IF(IsNull(black_or_white),Animal_ID))

Re: Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 11:39:00 GMT

Thanks for the explanation makes sense now. can get the

COUNT({<cat_or_dog = {'dog'}>}DISTINCT IF(IsNull(black_or_white),Animal_ID))

to work though. Ive swapped my variable names in, only difference i can see is that my field name is [black or white].

Also how would i check for not null?

Thanks

Re: Help plotting bar chart when an item is appears in two bars

Thu, 13 Aug 2015 11:42:01 GMT

Sorry i didn't see your second comment. that second expression worked. thank you so much!

Re: Help plotting bar chart when an item is appears in two bars

MK_QSL — Thu, 13 Aug 2015 11:48:14 GMT

Please close the thread by selecting correct answer...