Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Line chart help with date needed!

Hi I have made some progress since last time

I want to create a Line chart - ideally it should have the timeline on the X-Axis --- Years and Quarters, and on the Y Axis it should have the labour rate (that is the data I have).

I used a Cross tab initially to import the data. The data was in the form of:

Country Date      Labour Rate

Sweden Q1-2014 100

I separated the date by using the left() and right() functions. Now essentially I have four list boxes - Countries, Year, Quarter and Labour rate

My question now really is how to show it on the line chart. Ideally on the X axis it should be Q1 2010, Q2 2010... etc and on the Y axos the scale should be 80-100 (not a sum as that sums up all the quarters or year figure). And then we should be able to select country or countries to compare.

this is what I currently have.

Capture.JPG

This is the code i used - please be aware I am a newbie and not technical and therefore may have messed this up

Directory;

Table:

CrossTable(Quarter_Year, Labour_Rate)

LOAD Country,

     [Q1-2010],

     [Q2-2010],

     [Q3-2010],

     [Q4-2010],

     [Q1-2011],

     [Q2-2011],

     [Q3-2011],

     [Q4-2011],

     [Q1-2012],

     [Q2-2012],

     [Q3-2012],

     [Q4-2012],

     [Q1-2013],

     [Q2-2013],

     [Q3-2013],

     [Q4-2013],

     [Q1-2014],

     [Q2-2014],

     [Q3-2014],

     [Q4-2014],

     [Q1-2015]

FROM

[New File.xlsx]

(ooxml, embedded labels, table is [New RawDataLaborRates-OECD]);

NewTable:

NoConcatenate

LOAD [Country],

Right(Quarter_Year,4),

Left(Quarter_Year,2),

Labour_Rate

Resident Table;

-------------------------------------

Thanks! would appreciate the help! Even if ther eis a smart way for me to fix the excel file i am okay with that.

Also, for someone who is non technical and doesn't know coding - how would you suggest I learn qlikview development?

12 Replies
Highlighted

Re: Line chart help with date needed!

I think your going with the right approach ...since the values are more the chart shows like that.

You can use the combination of Quarter and Year to display according to the Quarter wise count.....Expression would remain same.

The best way to learn Qlikview is to Practice , qlikview Reference Manual...Example from the Qlik tech and the community will always help you to learn more 

Highlighted
Not applicable

Re: Line chart help with date needed!

Hi avinash!

Thanks for the help. Do you know how to use the combination to make Quarter and Year together? Im guessing you could do the same for Months and Year then also.     

Highlighted

Re: Line chart help with date needed!

As per your data , you can directly use Quarter_Year column , which is already a combination of Quarter and year.

If the values are more on X-axis you can use the scrolling option in the Presentation tab>Enable scrolling.


Since you don't have the month details, its good to create a Master calendar and like you quarter_year column with the master calendar so that you will get the month details

Creating A Master Calendar 

Highlighted
Not applicable

Re: Line chart help with date needed!

Hi Avinash,

I went over the video, that is quite complicated.

I did you suggestion with Quarters- now thing somehow would be to get the orders correct. the order now is Q1 2011,Q1 2012, Q2 2011, Q2 2012 - instead of getting them all yearly. hmmm.

Quarter Year order error.JPG

Highlighted

Re: Line chart help with date needed!

cool if you have got the answer please mark this thread as answered

Highlighted
Not applicable

Re: Line chart help with date needed!

I was thinking if there is a way to merge Quarter and Year again.

so if i merge Year and Quarter I get values such as 2010 Q1, 2010 Q2... and then use those to plot.

Alternatively is it possible to change Q1-2010 to somehow 2010-Q1?    

Highlighted

Re: Line chart help with date needed!

see you have already divided the column to year and Quarter rite combine the way you want.

Like

Right(Quarter_Year,4)&'-'&Left(Quarter_Year,2) as Year_Qarter,

Left(Quarter_Year,2)&'-'&Right(Quarter_Year,4) as New_Quarter_Year

in the load script , this should help you

Highlighted

Re: Line chart help with date needed!

Hi,

one solution could be:

QlikCommunity_Thread_179647_Pic1.JPG

Table:

CrossTable(QuartYearTemp, Labour_Rate)

LOAD * INLINE [

    Country, Q1-2010, Q2-2010, Q3-2010, Q4-2010, Q1-2011, Q2-2011, Q3-2011, Q4-2011

    Country1, 100, 200, 300, 400, 500, 600, 700, 800

    Country2, 110, 210, 310, 410, 510, 610, 710, 810

    Country3, 120, 220, 320, 420, 520, 620, 720, 820

];

Left Join (Table)

LOAD *,

    Dual(QuartYearTemp,MakeDate(Year,Quarter*3-2)) as Quarter_Year;

LOAD Distinct

  QuartYearTemp,

  Dual(Left(QuartYearTemp,2),Mid(QuartYearTemp,2,1)) as Quarter,

  Right(QuartYearTemp,4) as Year

Resident Table;

DROP Field QuartYearTemp;

hope this helps

regards

Marco

Highlighted
Not applicable

Re: Line chart help with date needed!

Hi Marco,

could you please follow me so I could ask you a question. I'm having a hard time understanding the code and would really appreciate some guidance.

Thanks