topic Re: Bar chart help please in QlikView

Bar chart help please

Wed, 25 Nov 2020 16:16:04 GMT

Hi QlikView gurus,

I need to take the following data and represent it as a bar chart where an Avg between "LCL" and "UCL" is represented as green, an Avg between "LSL" and "LCL" or "UCL" and "USL" is represented in yellow, and an Avg that is out of range is represented in red. I probably need to take another QlikView class, because I don't think that this should be as difficult for me as it is. Please help!

Re: Bar chart help please

sunny_talwar — Tue, 08 Mar 2016 20:49:10 GMT

Are we trying to implement this on the chart on SPC Analysis tab?

Re: Bar chart help please

rubenmarin — Tue, 08 Mar 2016 21:46:53 GMT

Hi Lee, I need more info to know what are you trying to distribute in each bar, records? days?... what is avg?

Assuming that this is counting records and avg is just the ActualNumeric field, I attach a version using mekko chart - the width of each bar can tell you how many records has any line -, If you need a bar chart or some other calculation formula, please provide more info about the requisites.

Re: Bar chart help please

Tue, 08 Mar 2016 22:14:09 GMT

Yes - on the SPC Analysis tab.

Re: Bar chart help please

Tue, 08 Mar 2016 23:04:09 GMT

I'm having trouble with my anti-malware software not allowing me to download qvw files from this site. I'm trying to fix the problem, but I haven't been able to open your qvw yet. The avg is a measurement for different tests that are conducted throughout the day. I basically just need to represent the count. In the below example, the test for Package Weight was higher than the USL all but once. So the chart for this range would show zero in the green zone, 1 in the yellow zone, and 15 in the red zone. I should probably also create one that presents the data in percentages.

I hope that was understandable. thank you again for your help!

Re: Bar chart help please

Wed, 09 Mar 2016 23:00:57 GMT

I was able to download the file. That was a big help - thanks! I'm curious about something else. The "TimeInspection" column in the chart is sorted daily midnight through 11:59 pm. Is there a way to sort that column so that every day appears as 6:00 am through 5:59 am instead?

Re: Bar chart help please

rubenmarin — Thu, 10 Mar 2016 18:51:23 GMT

Hi Lee, that will be kind of weird, isn't? ie:

TimeInspection
01/01/2016 6:00 am
01/01/2016 14:00 pm
01/01/2016 04:00 am
02/01/2016 6:00 am

Note the 26 hours gap between the last 2 rows, you want your dates sorted like this? If that's the requirement you can sort TimeInspection by expression in 'Sort' tab, using this expression:

If(frac(Time(Timeinspection))<1/24*6, Timeinspection+1/24*18, Timeinspection-1/24*6)

Btw, my expressions in last posts were wrong, I switched the fields. It's corrected in this post: Re: Calculated percentage in Bar Chart

Re: Bar chart help please

Thu, 10 Mar 2016 20:35:16 GMT

That's almost it. The example below is using your formula for the sort expression. 1st shift starts 3/1/2016 6:00:00 am and ends 3/2/2016 5:59:59 am, so that's how they want the data presented. thank you for your help!

Re: Bar chart help please

rubenmarin — Thu, 10 Mar 2016 20:50:39 GMT

Ok, maybe selecting a single day looks less annoying.

If that's what you're looking for, it can be simplified to:

If(frac(Timeinspection)<0.25, Timeinspection+0.75, Timeinspection-0.25)

Re: Bar chart help please

Thu, 10 Mar 2016 21:22:00 GMT

That's a whole lot closer than what I was coming up with, but it still puts 3/1/2016 5:32:52 am at the bottom of 3/1/2016, instead of with the previous day. The 6:30 to 10:43 is perfect, though.

Re: Bar chart help please

rubenmarin — Fri, 11 Mar 2016 00:19:21 GMT

Hi Lee, you said "Is there a way to sort that column so that every day appears as 6:00 am through 5:59 am instead?", so '3/1/2016 5:36:52 am' should be at the bottom.

That's the weird thing I was saying, there is a gap of more than 24 hours with the next record, or.. how it must be sorted?

Re: Bar chart help please

Fri, 11 Mar 2016 15:50:24 GMT

Hi - I'm sorry if I wasn't clear. It should be sorted so that a "day" is 3/1/2016 6:00 am - 3/2/2016 5:59 am; the next "day" would be 3/2/2016 6:00 am - 3/3/2016 5:59 am, etc.

Re: Bar chart help please

rubenmarin — Fri, 11 Mar 2016 15:58:23 GMT

Not sure if I'm misunderstanding something but this is the logical normal sort for a date, in 'Sort' tab try checking 'Numeric' so the order should be:

3/1/2016 6:00 am

3/1/2016 11:00 pm

3/2/2016 2:00 am

3/2/2016 5:59 am

3/2/2016 6:00 am

3/2/2016 12:00 pm

3/3/2016 5:59 am

Re: Bar chart help please

Fri, 11 Mar 2016 16:47:08 GMT

Oh wow. Thank you! I'm sorry I made that so complicated.