Line chart help with date needed!

[#1]

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.

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?

[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 

[#3]

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 

[#5]

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.

[avinashelite]

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

[#7]

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:

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

[#10]

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