histogram with X axis refernce lines, can it be done?

[wizardo]

hi there,

i know how to make histograms in qlikview using a bar chart with a calculated dimension with the Class() function

and it works great

but i need to add reference line, but when i go to the reference lines dialog window, the radio button is disabled from choosing the X axis (stating it needs to be a "continuous" axis)

apparently the calculated dimension in this case is not considered "continuous" (tho i think if you stretch the concept abit it might still fall within)

so i cannot use this feature.

anyone knows how to achieve this in qlikview?

if you are asking exactly what i need to do then here is a short description.

i have a model that reads data about people(customers and employees) entering the bank at various hours of the day. even at night and even when the bank is closed to the public.

i have made a bar-chart that uses the class() function to generate a 12 values dimension (X axis) that span 24hours per day with data for one week.

so what i get is 12 bars (0-2,2-4,4-6 etc) and each bar's height is the amount of people visiting the bank over the week distributed over the day span.

this works great.

now i need that each time the user select a subgroup of the people (just the customers or just the employees)

two reference line will appear (vertically), referencing the formal bank opening and closing times (they are different for each of the people types) and are located in two fields in the PeapleGroups table.

like this

group name | openHours | closeHour

customers 09:00 15:00

employees 08:15 17:00

hope this short description was clear and will help you solve my question

thanks

Mansyno

[johnw]

I'd probably handle it by making the chart transparent and putting two lightly-colored semi-transparent text boxes beneath it, where each displays if there are visits from people of that type. Attached is a quick-and-dirty version of what I'm talking about.

[wizardo]

hmm well i guess is my fault for not giving full info:)

your solution is good and iv used it in cases similar to the bank example.

the porblem is that it is static. meaning that the begin and end of day and the gap of the basket are equal for all groups and dont change

i guess its my fault for giving the bank example and i see now its not actuly covering all the problems

the real model i have to show is made out of results of tests been done to all the units of a product, these tests produce numeric values

for each test there is a set of high and low limits (the reference lines)

in the histogram i show for each test how the values of the results are distributed.

the problem is that for each test the span of values is difrent

some test produce values between 0.1 and 0.5

and some tests produce values between 2 and 20

and same goes for its high limit and low limits.

so this chart and the reference lines, i need them to be dynamic not static and change according to the low/high limit values for each test.

i guess i could caluclate the width of the chart and extract the highes and lowest posible values for each test

and then using a macro, to calculate and change the postion of the TEXT object you suggested as a replacment for the refernce lines.

but that sounds to much cumbersome for this.

i know it make sense that the refernce line on the Xaxis work only on continuous values but...

i think a basket is a type of continuous values.so i think qlikview whould be able to let me do this. but i guess it doesnt:)

Mansyno

[prieper]

If you hardcode the classes in the script and convert them into numbers or number likes you may use continuous axis and have your reference-lines displayed.

edit: basically the above is possible, but looks pretty ugly, when the gaps betw the individual intervals is longer. Thus a better solution is required.

Peter

[wizardo]

Hi Peter,

thanks for taking a try:)

i thought about doing the class in the script like you suggested but there are few issues with it (besides the look )

the gap or width of the basket is dynamic and based on the values themselves and not on some arbitrary number. plus i want the user to be able to change the gap interactively.(with a slider and a variable.

the second issue is that there are unknown number of tests and each test produces values that are different in span from other tests and might also be different on another set of records for the same test (one test might give values between 10 and 20 and another test will give values between 0.255 and 0.755. also one test might produce these values today but tomorrow will produce values *10% highr or lower.

hence i have no way of knowing what gap/width to create in the script for each test.

your solution is good as long as i have static gap like in the first example

or limited number of groups (tests).

again i feel the object itself (bar chart) should have dealt with the requirement a bit better.

histograms are an important tool and been able to set the boundaries of normality in the is essential in many cases.

i think there should be a way to add reference lines even if the axis is not continuous. qlikview could make a calculation of the width of the graph and use this as reference to allow the creation of lines (vertically)

but i might be wrong:) (I'm no expert)

thanks again

Mansyno

[johnw]

I'm unsure if I'm just making an example of what Peter was saying, since I use a continuous axis and "hardcode the classes in the script and convert them into numbers".

I do handle the fact that each type of test can have a different range of values. These and the reference lines are hardcoded in my script, but could just as easily be calculated based on actual test results. And once you do that, you can load in new types of test without needing to change the script.

I'm also handling the dynamic "gap" with a slider. You could also calculate a gap using the max and min from the tests, but I'm not sure quite how you'd combine the two.

It requires you to select a type of test to see, as it can only show one set of limits as designed, and since I'm not sure it makes any sense to overlay completely different distributions into one total. Not sure if you wanted to be able to see more than one test at once, and if so, not sure what you'd want it to look like.

It's not particularly clean, but it seems to show the data pretty well, and could probably be cleaned up a bit.

[#7]

Ok, so this post is a little stale, but I just ran into the same question, and was looking for other solutions.

Here is how I solved it:

I've used string functions to strip out the ceiling (the rightmost number) of each of the class() bins in order to get a numeric value. For the moment, I've just hacked it using right(class(, 3)) (all of my bin ceiling values happen to be three digits in length). You could do the same with string length functions, etc.

Once you've extracted the topmost value of the bin, you can set your axis to continuous, and add x-axis reference lines.

[rwunderlich]

Hi Ryan,

Class() values are dual values. That is, the internal format is numeric. The numeric value is the bin floor. So you should be able to select continuous axis without doing any stripping. Have you tried that?

-Rob