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

Variable X-axis

I have a dataset that I want to graph, for instance 'number of occurances / month', where a month without occurances should still show up.

I have created a master calendar for the date-field.

I have a set of data ranging from 2013 to today (jan 2016).

I have created variables for the current and previous year

Now I'm running into some problems when I try to graph out my occurances per month, since I want to also show the months where no occurances happened;

  • When I set the x-axis to continuous, I have to use a date-value (so for instance '=Month(DateField)') to graph on the X-axis instead of the MonthYear field from my master calendar. In this graph I can't get the X-axis labelled correctly (since it labels dates, I can't get the label to perfectly match with the bars on my graph (example1)
  • When I use the MonthYear field from my master calendar, I can either;
    • Show all values; which shows the values from 2013-2016 (example2)
    • Not show all values; but now the months without occurances are hidden (example3)

I would like a graph that looks like example1 (but works and labels with the MonthYear from my mastercalendar), or like example2, but then I would love to be able to set the graph to only show MonthYears that fall into the 2 variables (varCurrentYear and varPreviousYear) that I've set up.

What's the best way to approach this?

example1:

QV1.jpg

example2:

QV3.jpg

example3:

QV2.jpg

14 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Example 1 seems to be impossible because your bars overlap multiple months - QlikView doesn't do that.

Example 2 is easy to achieve if you declare your Axis as Continuous and format your months accordingly.

In order to limit your date range to the two selected Year, I'd suggest adding that as Set Analysis filters, it works better than doing the same in a Calculated Dimension.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Not applicable
Author

Exampe1 is possible, since i have managed to create it

-the used dimention is "month(DateField)", so it makes a single colum for every month. I just can't get it display a single label per month.

I gather my data with the following set anyalsis formula;

count({$<Type={'A','B'},[Date]={'*'},[YearDate]={$(varPreviousYear),$(varCurrentYear)}>}RegNr)

In Example 2 and 3 I already used the MonthYear as declared in my set analysis formula, but I can't set the x-asis to 'continous' when I use the MonthYear from my mastercalendar as a dimention.

I really can't figure out how I can label the 'continuous' x-asis with month labels, since it HAS to be a date field (and not a MonthYear field) for me to be able to set the axis to continuous in the first place.

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Continuous x-axis can be formatted in the Number tab. Format your date as a Month-Year:

MMM-YYYY or MMM-YY

cheers,

Oleg Troyansky

Not applicable
Author

I've tried that, but then I get multiple labels for every month.

I've tried setting a static step for the continuous axis. If I set it to '30' I do get a label for every month, but then they're not correctly aligned with the  bars on the graphs. Is there any way to get these labels to sit correctly under the bars?

Screenshot of the graph with the 'static step' on the axis set to '30';

QV4.jpg

Peter_Cammaert
Partner - Champion III
Partner - Champion III

On the other hand, a continuous axis will in theory produce all date values (that's why you get multiple identical months as representations of some of the date values), while a month-year value is by design a discrete value when based on a real date value. Serious conflict here.

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

So, if you are showing monthly bars, why does your dimension HAVE to be a date and not a month, again? I am afraid that you are creating your obstacle artificially...

Gysbert_Wassenaar

Can you post a small qlikview document that illustrates the problem?


talk is cheap, supply exceeds demand
MarcoWedel

Hi,

maybe one solution might be:

QlikCommunity_Thread_203538_Pic2.JPG

QlikCommunity_Thread_203538_Pic3.JPG

QlikCommunity_Thread_203538_Pic1.JPG

table1:

LOAD Date(MakeDate(2013)+Round(Rand()*(Today()-MakeDate(2013)))) as Date,

     Ceil(Rand()*100) as Value

AutoGenerate 15;

tabCalendar: 

LOAD *,

     -InYear(Date, Today(),0) as InCurrentYear,

     -InYear(Date, Today(),-1) as InPreviousYear,

     Day(Date) as Day, 

     WeekDay(Date) as WeekDay, 

     Week(Date) as Week, 

     WeekName(Date) as WeekName, 

     Month(Date) as Month, 

     MonthName(Date) as MonthName, 

     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 

     QuarterName(Date) as QuarterName, 

     Year(Date) as Year, 

     WeekYear(Date) as WeekYear;   

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD MakeDate(2013) as MinDate, 

     Today() as MaxDate 

AutoGenerate 1;

hope this helps

regards

Marco

Not applicable
Author

That's exactly the problem I'm running into.

I want to show the monthly bars for the years in my selection (so let's say I have data from 2013 upto today, and only want to show 2015), and I want the months within that selection that don't have any data to show up.

-if I don't use 'show all values' on the dimention the graph will only show the months that actually have data (example 3 from my original post)

-with the 'show all values' on the dimention it will also show 2013, 2014 and 2016 on the axis (example 2 from my original post)

When I use the 'continuous' option on the Axis settings tab, I have trouble aligning the labels since I assume "in the background" it's still an axis with a point for all dates, not just for the MonthYear labels I have (example 1 from my orignal post, or the graph from my previous post)

Unless I'm missing something completely obvious here (which is a valid possibility).