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

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
avinashelite

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 

Not applicable
Author

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.     

avinashelite

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 

Not applicable
Author

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

avinashelite

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

Not applicable
Author

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?    

avinashelite

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

MarcoWedel

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

Not applicable
Author

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