topic Re: How to have 2 fields in the X axis of a graph in QlikView

How to have 2 fields in the X axis of a graph

Thu, 21 Jul 2011 14:59:09 GMT

Hi!

Im new to Qlikview, and have the Personal Edition to see if its the right product for us. One test we are carrying out is replicating data and graphs we have in Excel into Qlikview.

Is it possible to have 2 fields in the X axis in a line graph? I have 3 fields, Day, Week of Year and Amount.

Im looking to have a graph that has Day and WeekofYear on the X axis, represented as the following;

Mon Tue Wed Thu Fri Mon Tue Wed Thu Fri Mon Tue Wed Thu Fri

Week 1 Week 2 Week3

At the moment, when I add Day and WeekofYear in the dimensions, it puts Day in the X axis, and treats WeekofYear as a legend entry.

Is there any way to achieve this? Its a very important measure for the company I am trying to create, and will probably be the a key factor on if we purchase the product.

Thanks!

How to have 2 fields in the X axis of a graph

Thu, 21 Jul 2011 15:12:48 GMT

Normally there is something called a Cyclic Group whcih is used in between days, months and years. But if you want to see both the dimensions at onetime, then you can create a dimension field with with a concatenation of day and week and use it as a dimension.

How to have 2 fields in the X axis of a graph

swuehl — Thu, 21 Jul 2011 15:13:00 GMT

Add a second expression and make it invisible should do the trick.

Regards,

Stefan

How to have 2 fields in the X axis of a graph

Thu, 21 Jul 2011 15:19:18 GMT

I've tried both suggestions, and they dont work.

Swuehl, how will adding a second expression give the x axis a second layer?

Re: How to have 2 fields in the X axis of a graph

swuehl — Thu, 21 Jul 2011 15:24:33 GMT

Hi,

if you have only one expression, the second dimension will be used as label, with two or more expressions, you get something I think you intend to get.

Please look at my attached image.

I used two dimensions, week# and day of week.

One expression is count of something, one is 1 and made invisible.

Regards,

Stefan

Nachricht geändert durch swuehl

How to have 2 fields in the X axis of a graph

Thu, 21 Jul 2011 15:26:42 GMT

Hello,

" create a dimension field with a concatenation of day and week and use it as a dimension" ----

This should work for you as i had a similar requirement in the past. Even now when I have to show data for Year and Months, I dont use 2 dimensions, instead I created a new dimension in the script which is called YearMonth and then use it. however you need to do it in the master calendar or your Time periods(Calendar) Table.

Thanks

How to have 2 fields in the X axis of a graph

Thu, 21 Jul 2011 15:31:40 GMT

That works with a bar chart, but I need a line graph.

I've attached the line graph I've created in excel...

How to have 2 fields in the X axis of a graph

swuehl — Thu, 21 Jul 2011 17:55:49 GMT

Sorry, I missed your line chart requirement.

I tried for a while and also searched in the forum, and couldn't get it to work exactely as you require.

Furthermore, it seems that QlikView has some issues with line charts / combo charts with more than one dimension, for example if I do a combo with week# and day of week as dimension and count something as expression as line and bar chart, the line look pretty strange.

So I think a good solution is to use Mady mama's suggestion with a concatenated dimension field, I also use this quite often.

Regards,

Stefan

I think since Version 10, there is the possibility to use extension objects (like 3rd party sheet objects, extending the available graph objects). There might be an extension object which may fulfil the requirements, but haven't looked for that.

Re: How to have 2 fields in the X axis of a graph

johnw — Thu, 21 Jul 2011 23:53:36 GMT

It's a very basic capability, but I don't think QlikView does it. As mentioned, concatenating the day and week would work fine and is a typical solution. The only thing is that it will repeat your week for every date, like 'week 27 Mon 2' 'week 27 Tue 3' and so on. You can sort of work around this if it really bothers you. I've attached an example with years and months as my "dimensions", and the year only displaying next to the first month of that year included in the chart, but you have personal edition so can't see it, so...

The script:

LOAD *
,month(YearMonth) as Month
,year(YearMonth) as Year
;
LOAD
date(addmonths(makedate(2009,1,1),floor(rand()*24)),'YYYY MMM') as YearMonth
,ceil(rand()*10) as Amount
AUTOGENERATE 100
;

The line chart:

dimension=aggr(date(YearMonth,if(Month=min(total <Year> Month),'YYYY MMM',' MMM')),Year,YearMonth)
expression=sum(Amount)

It's possible that things are better in version 10 or 11. I mostly use version 9.

Re: How to have 2 fields in the X axis of a graph

morenoju — Mon, 22 Apr 2019 13:24:17 GMT

Is it possible to achieve this in Qlik Sense? So far I haven't find any chart or extension that allows two dimensions the way described in this thread.

Re: How to have 2 fields in the X axis of a graph

morenoju — Tue, 23 Apr 2019 12:12:32 GMT

For future reference: I've been trying to do this in Qlik Sense and so far the answer has been like the one Johnw provided 8 years ago (concatenating).
https://community.qlik.com/t5/New-to-Qlik-Sense/Two-dimensions-on-x-axis/m-p/1572058