Line chart: Secondary x- axis?

[#1]

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?

[swuehl]

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.

[Gysbert_Wassenaar]

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.

[cjohnson]

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.

[stephenedberkg]

U should add the dimension as month or day

and writer the expression for current month vs Last month

then only you can compare

[#6]

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.

[#7]

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.

[cjohnson]

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

[#9]

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.

[swuehl · Accepted solution]

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.