Two labels on the X axis of a Trellis Chart.

[#1]

Hello all!

I have the following case and I can't find the solution. I will appreciate if someone can give me a hand 🙂

I have an accumulative bar chart with 3 dimensions.

Year, Week and Type (this one is a cyclic group).

The chart looks like I want, with two labels on x Axis:

When I turn this graphic into a Trellis chart, in order to see one line for each type, I obtain this useless chart:

Because of this I'm forced to use a unique label Year+Week and it's no look very good.

It's possible to accomplish this? Someone has any idea?

Remarks,

The year and week are not calculates fields. The original data has year and week on the table.

Thanks.

Regards,

Cristian

[pover]

Cristian,

It seemed to work fine for me. Review the attached application to see if I'm missing something.

Your problem might be that your object is a defined as a combo chart and not a bar chart.

Regards.

[johnw]

Not sure if this is quite what you're asking for, but here's an example of only showing the year on the first month in the chart displayed for that year. It's just a calculated dimension like yours at heart.

Though frankly, I'd just add a "Year Week" field to your calendar, use that as the dimension, and rotate the value to display vertically. I don't see why it matters much that 2010 is only displayed once instead of on every data point.

Hmmm, if I change my example to a trellis chart with year as the first dimension (because I don't have any other appropriate field), it demonstrates a weakness of the approach - if that first month of the year isn't displayed, then you won't see the year. You can stretch out the chart so that it does display the year, but it just seems risky. It seems safer to just show the year on every data point.

And I may just be missing an obvious and easy solution. I almost never touch trellis charts, and I'm on version 9.

Edit: So I WAS missing an obvious and easy solution, which is that it works fine in version 10. 🙂

[#4]

Normal 0 21 false false false ES-AR X-NONE X-NONE MicrosoftInternetExplorer4

Hello Karl, Thanks for your answer.

I've attached your example where I added "Fast Type Change" for "line chart". When I 'have bars it works perfect, like yours. Instead, look what happens when you turn your chart to type "line". That's the problem I have. Have you seen it before? In that case, have you found solution?

Best Regars,

Cristian

[#5]

Normal 0 21 false false false ES-AR X-NONE X-NONE MicrosoftInternetExplorer4

Hello John,

The approach you have used it's very ingenious and think is enough for what I need. I didn't understand the following.

"Hmmm, if I change my example to a trellis chart with year as the first dimension (because I don't have any other appropriate field), it demonstrates a weakness of the approach - if that first month of the year isn't displayed, then you won't see the year. You can stretch out the chart so that it does display the year, but it just seems risky. It seems safer to just show the year on every data point."

Probably I have confused the names of the charts, trellis doesn't mean "line chart"?, Your approach works well on the line chart of your example (I also have the version 9).

The problem I have trying to implement your method is that you uses the "date" function and it doesn't seem to work with weeks. I not sure how to implement it. Could you give some orientation?

Best regards,

Cristian

[johnw]

Let me first explain my comment with the picture below:

The months of Feb and Apr are for 2010, but there's no 2010 shown. The reason is that only 2010 Jan will show, since Jan is the earliest month in the chart for 2010. There's no way for me, in an expression, to know whether or not the LABEL for that month can be displayed in the chart, though. It's not strictly a problem with a trellis chart; it's easy to shrink a regular bar graph (or line graph) down to where 2010 Jan isn't shown. But it's more likely to be a problem with a trellis chart since it makes a bunch of really small charts. In any case, this problem is why I recommend just using a "Year Week" field and thus showing the year on every single label.

I'll try to put together a week example if I get a little time today.

Edit: Ah, no, trellis doesn't mean line chart. Trellis refers to how Customer A and Customer B are shown side by side, each with their own mini chart. You can have a trellis bar chart, a trellis line chart, a trellis pie chart, and so on.

[johnw · Accepted solution]

OK, the problem with using a date function on your week probably has to do with how you've defined your week.

I always avoid week numbers and define my weeks as dates. Normally, I just use the start date for the week:

date(weekstart(Date)) as Week

But if you want a week number, that's unfortunately not a supported format for date displays. So you might be doing something like this:

year(weekstart(Date)) & ' W' & week(Date) as YearWeek

If so, that's just a string expression, so you can't use date functions on it. However, you can probably fool QlikView by doing this:

dual(year(weekstart(Date)) & ' W' & week(Date),weekstart(Date)) as YearWeek

That says to use your string for display purposes, but to also store the first day of the week as the underlying value. That should let you use date functions on it.

Ah, except that I'm using date() specifically to format, and again, there's no format code to display a week number. Still, doable, you just have to put an entire expression in the if instead of a format. I think this?

,week(Date) as Week
,year(weekstart(Date)) as WeeksYear

=aggr(if(Week=min(total <WeeksYear> Week),YearWeek,' W' & Week,WeeksYear,YearWeek)

But the specifics depend on how your week is defined, of course. So how IS your week defined?

And again, I wouldn't do this. I'd just use YearWeek as my dimension.

[#8]

Hello John. Thanks for your help, I very appreciate it!.

Yesterday, by trial and error, I tried with this expression:

aggr( if(Week=min(total <Year> Week), weekname( MakeWeekDate(Year,Week)) , Week), Year, Week)

but, like you have said, it has the problem that the week isn't a date and QV take it as a number. So, the weeks after the first one in the year it's only a number and QV doesn´t distinguish at what year belong. The result is something like this, where for example the week 5 is the total for all the years. And for some reason, weeks are not in order.

Normal 0 21 false false false ES-AR X-NONE X-NONE MicrosoftInternetExplorer4

Your idea of making a week with a date underlying is very good (I didn't know about this command), but for some reason it's given me the same result. Apparently the underlying is making any effect.

Like you said, probably I will use a field YearWeek for now and I will wait for QV supports double label on line charts 🙂

Thanks for your time and dedication with my problem!!

Regards,

Cristian

[#9]

Well, affect sometimes doing other things I returned to QV and tried again :). It works perfect. Thanks for the ideas John, I couldn't make without your help!!.

The final expression is:

=dual(aggr( if(Week=min(total <Year> Week), Year & ' ' & Week , ' ' & Week) ,YearWeek), YearWeek)

Where, when I load the data:

dual(Sem,weekstart(MakeWeekDate(Anio,Sem))) as YearWeek

Year and Week are directly the data of the excel file and are not dates. There are simple numbers.

Again, Thanks for all!!

Regards,

Cristian