topic How to get Vertical reference lines parallel to Y axis ? in QlikView

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 08:29:57 GMT

Hi folks,

I am having little problem displaying Reference lines parallel to Y axis. Here is the description:

I have a filed "Month" which holds data for 3 years (in format like Jan-10 through Dec-10,Jan-09 through Dec-09,Jan-11-Dec-11).I want to plot expression (say Sum(Sales)) against this dimension and it is working all fine. Now I want to have reference lines at the end of each year i.e. on Dec-09, and Dec-10 . The "Month" field in not a calculated dimension and is directly from DB.

I have attched the sample layout below.Need your suggestions.

How to get Vertical reference lines parallel to Y axis ?

shumailh — Thu, 22 Jul 2010 08:37:39 GMT

Have you tried YearEnd() function on your reference line?

Regards,
Shumail Hussain

How to get Vertical reference lines parallel to Y axis ?

kji — Thu, 22 Jul 2010 08:39:48 GMT

Assuming you are using a continuous X-axis which it looks like from your example, on your reference line make sure location is set to continuous X.

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 08:45:00 GMT

Hi Shumail,

I have tried that too but then graph is disappearing and I am getting the error saying "No Numeric values on X-axis" .... Any thing else ???

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 08:47:46 GMT

Hi Johan,

Yes! Location is set to continuous X only.... but no luck

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 08:52:51 GMT

Also one more weird thing is happening. As soon as I am selecting "Continuous" on Axes, the graph is not displaying anything and saying "No Numeric values on X-axis" . so that mean my Dimension is not continuous ??? How to solve this ?

How to get Vertical reference lines parallel to Y axis ?

kji — Thu, 22 Jul 2010 09:23:47 GMT

Sounds like your X-dimension is using string values, not numeric values. Check in the script if you can read it as dates instead, or if that information is already available in some other field.

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 10:13:45 GMT

You are right Johan. My X-dimension is using string values like Jan-10,Feb-10 etc. And i need to use this only in the dimension. So can we change somehow this field to Numeric or to continuous ?

How to get Vertical reference lines parallel to Y axis ?

jonathandienst — Thu, 22 Jul 2010 12:29:22 GMT

Load month using

Date(Date#([Month], 'MMM-YY'), 'MMM-YY') AS [Month]

This will give you a numeric date in the same format.

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Thu, 22 Jul 2010 13:23:33 GMT

Jon,

Thx for giving it a shot. But by using the above formula also, I am not getting desired result

this is again giving me date as I alrady have .

How to get Vertical reference lines parallel to Y axis ?

jonathandienst — Fri, 23 Jul 2010 08:18:10 GMT

Can you upload something so we can have a look at it?

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Fri, 23 Jul 2010 08:43:01 GMT

Hi,

I have attached a sample apps.... in this I am using Months and Sales.I want the refrence lines parallel to Y axis when a year ends i.e. at Dec-10 and Dec-11, it should show two lines.

Thanks fr giving it a shot.

How to get Vertical reference lines parallel to Y axis ?

jonathandienst — Fri, 23 Jul 2010 10:11:29 GMT

OK, got it working with reference lines up to two years. See if this is what you want.

I have included a straight table that shows how you need to handle dates if they are in string format like in this example.

I would convert the Month field to proper dates durng the load as I suggested in a previous post. This would make the expressions on the reference bars a lot simpler.

You will notice that the dimension of the chart is now a calculated value that converts the string to a date, and the x axis is continuous.

The reference bar expression calculate the minimum year and paint a bar at min rank 0 and min rank 1. The '- 15' paints the reference lines between the bars on the chart.

Jonathan

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Fri, 23 Jul 2010 10:32:17 GMT

Hi Jona,

Thanks much. I did the similar way, and after changing to calculated dimension, Dates are displaying in very weird fashin on X-axis.Please see below. How can i fix this issue now ?

How to get Vertical reference lines parallel to Y axis ?

jonathandienst — Fri, 23 Jul 2010 10:36:49 GMT

Set the number format for the "Continuous X-axis" in the chart Properties | Number tab to the date format you need.

Jonathan

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Fri, 23 Jul 2010 11:04:40 GMT

Thx Jona. But the graph I am getting is very wierd now. I dont think it is right one.Is making axis to "Continuous" causing this problem ? See below:

How to get Vertical reference lines parallel to Y axis ?

kji — Fri, 23 Jul 2010 12:04:35 GMT

Make sure your sort-order for the Dimension is Numeric

How to get Vertical reference lines parallel to Y axis ?

montubhardwaj — Fri, 23 Jul 2010 13:13:27 GMT

Finally...!!!! Got it guys... Thx Jona and Johan and all u member for ur help....!!!

Jus small doubt, after getting reference lines successfully, Month are appearing like Jan-10,Mar-10,May-10,Jul-10 etc i.e it is skipping one month in between. I dont have any problem with this but just asking out of curiosity

How to get Vertical reference lines parallel to Y axis ?

kji — Fri, 23 Jul 2010 13:52:19 GMT

This is probably due to not having enough space to print each month without texts overlapping, if you make chart wider i think all months will appear.

How to get Vertical reference lines parallel to Y axis ?

lmonincx — Fri, 20 Aug 2010 15:22:02 GMT

Hi Jonathan, Can you tell how you managed to get the X- axis working, in my qvw it's greyed and i can't get it selectable in a barchart?

Kind regards,

Linda