topic Re: Re: Bar chart with bucket, two dimensions, and one expression in QlikView

Bar chart with bucket, two dimensions, and one expression

Thu, 24 Apr 2014 19:12:33 GMT

Hi all,

I have a question that I think involves IntervalMatch for two separate types of counts of objects, but I'm not sure. Any suggestions or tips would be great!

I need to create a grouped bar chart that has two sets of bars - one for a bucket for a count of items between 0 and 5, and a second bucket for a count of items between 6 and 10. I need to count two different types of items (a count of all serial numbers and a count of activated serial numbers). Then I total the number of companies for each of these two metrics. I imagine the chart will have four bars, grouped in twos. Group 1 will represent 0-5 for both metrics, and Group 2 will represent 6-10 for both metrics. Metric 1 will be Color1 and Metric 2 will be color 2, in both groups of bars. The expression is a count of companies that have a total of all serial numbers and activated serial numbers in that bucket.

Hopefully that makes at least some sense. I'm attaching a mock-up in Excel to help explain what I am visualizing and understand I am being asked for.

Thanks!

Robin

Re: Bar chart with bucket, two dimensions, and one expression

MK_QSL — Thu, 24 Apr 2014 19:16:12 GMT

Kindly provide sample data...

Re: Re: Bar chart with bucket, two dimensions, and one expression

Thu, 24 Apr 2014 20:47:42 GMT

Sure, here you go. The data structure for this is actually pretty simple - all three columns are in the same table - CompanyName, CountTotalSNs, CountActiveSNs. See attached (field names are slightly different there), on a separate worksheet. Thanks for the help!

Robin

Re: Bar chart with bucket, two dimensions, and one expression

Thu, 24 Apr 2014 22:28:47 GMT

May you provide in your xls what result should be for your sample data?

Is it possible, that company has more active than total SN?:

Company 1	3	6
Company 2	2	7

What if active is less than 6 and total is more than 5?

regards

Darek

Re: Bar chart with bucket, two dimensions, and one expression

MarcoWedel — Thu, 24 Apr 2014 23:24:21 GMT

can you explain, how the companies are grouped into the categories "0-5" and "6-10"?

Re: Bar chart with bucket, two dimensions, and one expression

Fri, 25 Apr 2014 13:39:20 GMT

Doesn't matter, really. I just generated sample data quickly for folks to see.

Re: Bar chart with bucket, two dimensions, and one expression

Fri, 25 Apr 2014 13:42:42 GMT

Yes, sorry, that's important. Companies are grouped into the 0-5 and 6-10 buckets based on the number of total serial numbers, and active serial numbers. So Company 1 would fall into the 0-5 bucket for Total SNs and the 6-10 bucket for the Activated SNs. Company 5 however would fall into the 0-5 bucket for both Total SNs and Activated SNs. Thank you all!

Institution	# Total SNs	# Activated SNs
Company 1	3	6
Company 2	2	7
Company 3	5	5
Company 4	1	1
Company 5	2	2
Company 6	3	3
Company 7	4	4
Company 8	5	5
Company 9	6	6
Company 10	10	1

Re: Bar chart with bucket, two dimensions, and one expression

Fri, 25 Apr 2014 13:42:58 GMT

so please provide values (on your chart in xls ) you would like to see for this particular case.

I try to understand what is the logic to decide which row to which group on the chart ....

regards

Darek

Re: Bar chart with bucket, two dimensions, and one expression

Fri, 25 Apr 2014 13:50:57 GMT

So, one more question

do you think that number of your buckets (now there are two) and ranges for them (now we have 0-5 for first and 6-10 for 2'nd) will be static, or you think about more dynamic and generic approach - for example you would like to read buckets and ranges for them from some xls ... ?????

regards

Darek

Re: Re: Bar chart with bucket, two dimensions, and one expression

Fri, 25 Apr 2014 14:29:48 GMT

For me it may be good direction.

Let me know what do you think about it.

regards

Darek

Re: Bar chart with bucket, two dimensions, and one expression

Sat, 26 Apr 2014 16:36:55 GMT

From what I understand from the user providing requirements, the buckets are actually "less than 5" and "more than 5", so I think the answer is no, I don't expect any additional buckets. You were wondering whether using class() would be necessary maybe?

Re: Bar chart with bucket, two dimensions, and one expression

Sat, 26 Apr 2014 16:48:23 GMT

Hey, that looks great! As I mentioned above, the only other small piece of info I need to mention is that the user would like to see only two buckets - basically "less than 5" and "more than 5", rather than buckets that are increments of five. Would that be possible, do you think?

Thank you so much!

Re: Re: Bar chart with bucket, two dimensions, and one expression

Sat, 26 Apr 2014 20:12:43 GMT

Ofcourse, and even easier

Re: Re: Re: Bar chart with bucket, two dimensions, and one expression

MarcoWedel — Sat, 26 Apr 2014 23:00:55 GMT

Hi,

looks very good.

One suggestion to simplify the script though:

tabSNs:
CrossTable(SNType, #SNs)
LOAD Institution,
    [# Total SNs] as Total,
    [# Activated SNs] as Activated
FROM [http://community.qlik.com/thread/115527]
(html, codepage is 1252, embedded labels, table is @2);

regards

Marco

Re: Bar chart with bucket, two dimensions, and one expression

Sun, 27 Apr 2014 17:21:47 GMT

Thank you both so much! I have what I need now! I very much appreciate the help!

Best regards,

Robin