14 Replies Latest reply: Apr 27, 2014 1:21 PM by Robin Cushman

# Bar chart with bucket, two dimensions, and one expression

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

Kindly provide sample data...

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

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

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

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

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

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

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

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

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: Bar chart with bucket, two dimensions, and one expression

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: Re: Bar chart with bucket, two dimensions, and one expression

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

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

Ofcourse, and even easier

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

Hi,

looks very good.

One suggestion to simplify the script though:

```tabSNs:
CrossTable(SNType, #SNs)
[# Total SNs] as Total,
[# Activated SNs] as Activated
(html, codepage is 1252, embedded labels, table is @2);
```

regards

Marco

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

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

Best regards,

Robin