Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

10 Replies
Not applicable
Author

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.

swuehl
MVP
MVP

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

Regards,

Stefan

Not applicable
Author

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

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

swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

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

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

untitled.JPG

swuehl
MVP
MVP

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.

johnw
Champion III
Champion III

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.

morenoju
Partner - Specialist
Partner - Specialist

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.