topic Re: Trellis chart: How to generate variable y-axes in QlikView

Trellis chart: How to generate variable y-axes

andreas_koehler — Mon, 15 Feb 2016 22:28:02 GMT

Hi !

I found references to this issue but they were asked in 2010 and not answered. So hopefully someone had a great idea of how to resolve this:

I need to have a flexible, dynamic way to show a bunch of line charts based on user input. They should all have the same x-axis (time) but should show real values for each second dimension on the y-axis. These values can differ significantly, so I need different ranges in the y-axis based on the values of the second dimension.

Trellis charts are great but they share their y-axes.

How do I achieve different ranges for y-axes in trellis charts?

Is there an alternative to Trellis charts?

As always: Thanks for your interest and any replies.

Andreas

Re: Trellis chart: How to generate variable y-axes

Mon, 15 Feb 2016 22:49:49 GMT

Maybe try a container and change the container type to grid. It would give the appearance of a trellis, but with autonomous charts so you could change them individually to fit your needs.

Re: Trellis chart: How to generate variable y-axes

andreas_koehler — Mon, 15 Feb 2016 23:49:31 GMT

Caleb,

thanks for your reply.

Sadly I do not see how this solves the problem. I can not create the diagrams beforehand. They need to be created on-the-fly due to user input: Both selection and number vary.

The only work-around I see right now is to denominate the values but that is not what I was aiming for.

Re: Trellis chart: How to generate variable y-axes

Tue, 16 Feb 2016 14:24:18 GMT

I don't think I understand your requirement. Can you attach an example of your desired outcome?

Re: Trellis chart: How to generate variable y-axes

andreas_koehler — Wed, 17 Feb 2016 11:15:57 GMT

Caleb,

thanks for your interest.

I would like to illustrate this with the following example:

You want to compare stock prices from hundreds of listed companies. The price per share ranges from USD 0.012 for MVTG to USD 193,465.00 for BRK. Some companies get delisted, some get listed, which you add to your data dynamically. Now you want to give the user the possibility to compare the chronological change in price of several stock he selects. He want to see a not pre-defined number and not pre-defined selection of charts that share the range in the x-axis. So time is one dimension and the stock ID the second dimension.

But with a Berkshire Hathaway in your selection you would only see horizontal lines for basically all other stock, wether APPL or MVTG. For this you need different y-axis.

I like the idea of a container but how would you keep the number and the selection variable?

Re: Trellis chart: How to generate variable y-axes

Thu, 18 Feb 2016 14:49:36 GMT

Hi Andreas,

Have a look at the attached. Does this come close to meeting your requirement? Let me know if it doesn't and we can try to come up with something else.

Thanks

Re: Trellis chart: How to generate variable y-axes

andreas_koehler — Fri, 19 Feb 2016 20:50:54 GMT

Caleb,

thanks for not giving up. You are a true house builder 😉

Temporary I only have the personal edition available, so i can not open your file. Can you attach a screenshot?

Andreas

Re: Trellis chart: How to generate variable y-axes

Fri, 19 Feb 2016 21:34:43 GMT

I had to create 4 data islands to attach each chart to it's own filter. Attached is the script

Here is the formula that is used to attach the set analysis to the list box:

sum({<Symbol={$(=replace(Concat(Chr(39)&GetFieldSelections(SymbolFilter1)&Chr(39),','),', ',Chr(39)&','&Chr(39)))}>} Close)

You can obviously change to layout to something more elegant, but hopefully this is a starting point.

Re: Trellis chart: How to generate variable y-axes

MarcoWedel — Fri, 19 Feb 2016 23:07:26 GMT

Hi,

maybe you could use a trellis chart, show each price relative to its start value and mark min and max with absolute values:

hope this helps

regards

Marco

Re: Trellis chart: How to generate variable y-axes

andreas_koehler — Sat, 20 Feb 2016 21:06:15 GMT

Caleb,

What a nice piece of scripting. Kudos to you!

Several charts each with an individual filter in a container. And I can also assign several symbols to one chart. Having about 4-6 charts on one page should also be fine. I really like the twist with the replace to include the ',' between the symbols.

By the way: How do you check these rather complex scripts while you create them? Presently I use a textbox to build the syntax and check the outcome starting with the inner element, but textboxes lack the automatic syntax check.

I also like Marco's proposal but yours is the one that is more to what I orignally had in mind.

Thanks again.

Andreas

Re: Trellis chart: How to generate variable y-axes

andreas_koehler — Sat, 20 Feb 2016 21:19:28 GMT

Marco,

very neat piece of scripting. I also thought about denominating it with the start value as work-around. To stay with this example, this is what for stocks sites like google do. I could not figure out how to create and position the min and max near the lines. I wonder how the parallel use of % to start value (which is most of the time quite arbitrary) and the absolute number is perceived by users. I looked at this solution now for some time and I start to like it. The variability could also be expressed using some greeks.

Thanks for this solution.

Well, two very nice solutions for a problem that was in the beginning quite frustrating.

Re: Trellis chart: How to generate variable y-axes

Mon, 22 Feb 2016 13:55:14 GMT

Andreas,

Glad we found a solution!

I find the best way to test these complex expressions that use a dollar-sign expansion, is to use a straight table and leave the expression label blank. That way the evaluated expression is shown in the label. From there you can usually figure out what might have gone wrong. Below you can see what the expression used in this thread expands to using the straight table. Don't stop using text boxes however, they are very useful if you need to test smaller parts of your expression - in this expression you could test the Concat() and Replace() parts to make sure they are returning the correct string.