topic Re: Line chart: Secondary x- axis? in QlikView

Line chart: Secondary x- axis?

Wed, 23 Sep 2015 14:29:28 GMT

Hi,

I need to overlay two lines on top of each other for comparison purposes. These two lines measure the same metric but in two different periods of time, which is what is being compared.

I have only managed to come up with this:

Now this is not very useful for comparison, especially when the gap between the red and blue lines widens (because I selected a period way in the past for example).

What I need is to overlay these two lines on top of each other, so that I can compare the trend. That would mean that I need a seperate x-axis for each line, and have these 2 x-axis within the same graph area. In this particular example, one axis would have values for 09/2015, while the other for 08/2015.

This is what I need (watch out for Paint):

Having 2 y-axis within the same graph, but I could not find a way to do the same thing for the x-axis.

Any tips?

Re: Line chart: Secondary x- axis?

swuehl — Wed, 23 Sep 2015 15:56:50 GMT

You can try to overlay two line charts with transparency.

But I would suggest using something like this:

Use two dimensions, Day (Day of Month) and Year-Month, and your expression.

This should create two lines over the common dimension Day.

Re: Line chart: Secondary x- axis?

Gysbert_Wassenaar — Wed, 23 Sep 2015 16:00:28 GMT

You can't display a dimension on two axes. Create a new field from the date that holds the day of the month of the date: Day(MyDate) as DayOfMonth. Then use that new field as dimension.

Re: Line chart: Secondary x- axis?

cjohnson — Wed, 23 Sep 2015 18:05:49 GMT

Hi dtheodor,

You can get 2 x axis by having 2 dimensions as shown below:

But I'm not sure if there is a way to get one date dimension on top and the other at the bottom.

Attached is an example of what is shown above (which I believe is the same suggestion as made by Gysbert above.

Re: Line chart: Secondary x- axis?

stephenedberkg — Thu, 24 Sep 2015 05:35:29 GMT

U should add the dimension as month or day

and writer the expression for current month vs Last month

then only you can compare

Re: Line chart: Secondary x- axis?

Fri, 25 Sep 2015 13:30:29 GMT

Hi Camile,

I am looking into your qvw, but I can't seem to make sense of the data that is actually displayed. In particular, I updated the qvw with some more "traditional" line charts that use a single dimension with both values. The lines I get there do not correspond to the lines I get in your graph with the two dimensions. Superficially they may seem that they do, but if you look closely the lines always differ. I think that the data displayed in the 2-dimension graph is bogus. If you have any clues please let me know.

Re: Line chart: Secondary x- axis?

Fri, 25 Sep 2015 13:32:43 GMT

Hi, thanks all for the responses. Unfortunately I was not able to use the method you suggested for any meaningful result. I attach a very simple qvw with 2 metrics and 2 dates that I want to compare by overlaying the two lines. If someone can give any pointers in what I am doing wrong I would be grateful.

Re: Line chart: Secondary x- axis?

cjohnson — Fri, 25 Sep 2015 13:45:38 GMT

Hi dtheodor,

I think I misinterpreted your requirement. You only have one date in your data model? (Well I see you derived to dates - but they are really being driven off of the same date shown below):

In the example you mocked up above - what exactly are you looking to show? I made the assumption that you had two separate dates in your data model - so the example I mocked up generated two separate dates based off of a random number - and then has the data plotted against those 2 dates. Your example only had one date...

So in your data are you expecting the red line to start on 2/4?

Camile

Re: Line chart: Secondary x- axis?

Fri, 25 Sep 2015 14:24:09 GMT

The red line to start on 2/4, exactly

To get into more detail, not only do I have one date but the metric is also the same. The difference between the two expressions is the set analysis that limits each expression to a different period (something like sum({date >= period1start, date <= period1end} value) ). The goal here is to compare the same metric over 2 periods of time chosen by the user.

Re: Line chart: Secondary x- axis?

swuehl — Fri, 25 Sep 2015 15:32:12 GMT

That's non trivial.

The suggested approach above using Day() would be feasable if you are comparing time similar time periods, like Month A vs. Month B. If the user may select any number of days, potentially crossing month borders, and even a different number of days in both selections, then this approach would probably not fit your needs.

Nevertheless, I added a chart to your sample QVW that demonstrates what I initially envisioned.

I also added a more complex approach that may fit your detailed requirements.

Note that I changed the script to better match what I think you described as your real setting.

I've added an alternate state and a two list boxes for date so the user can select two date ranges.

in the bottom left line chart, I've added a synthetic dimension and some logic to the two expressions, so they both can use a common dimension to draw the data.

Re: Line chart: Secondary x- axis?

Wed, 30 Sep 2015 10:48:19 GMT

Thanks, that looks like a solution in the right direction. I was not aware of the alternate state feature until now.

What's missing now is an improvement in how dates are distributed over the ValueLoop() dimension so that both generated dates can span the full length of the x-axis of the graph. Right now if one period is 1 month and the other period is 2 months, the graph corresponding to the 1 month spans only half of the x axis while the 2nd one spans the whole x-axis. After this change, the feature will fulfil the requirements fully. I'll give it a try.

Re: Line chart: Secondary x- axis?

Thu, 01 Oct 2015 10:19:38 GMT

I achieved some improvement over how the dates span the x-axis. Here's the result:

The 6 days of the red period are extended to span the 20 days of the blue period, although this distribution could be done better to span the rightmost points. Also, the dates that appear as text on the axis get too cluttered; after a brief effort I wasn't able to space them apart (by omitting some values)

Re: Line chart: Secondary x- axis?

swuehl — Thu, 01 Oct 2015 14:12:24 GMT

If you are going to stretch the red line, the slope of the red line can't be compared directly to the blue one.

You can't even easily read from the chart the value of both lines after same x days after period start.

What information do you want to retrieve from this chart then?

Re: Line chart: Secondary x- axis?

Fri, 02 Oct 2015 07:51:48 GMT

By stretching the line you enable the trend comparison. (as long as the curvature is maintained and the line is not skewed differently than the original).

Re: Line chart: Secondary x- axis?

Rodney — Wed, 14 Nov 2018 22:35:52 GMT

Hi,

sorry to reply to an old thread but no one has replied to the new one I created here

Does anyone know how to do this in QlikSense?

Thanks in advance.