topic Re: Line chart help with date needed! in QlikView

Line chart help with date needed!

Fri, 20 Nov 2020 18:02:30 GMT

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?

Re: Line chart help with date needed!

avinashelite — Tue, 08 Sep 2015 12:29:54 GMT

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

Re: Line chart help with date needed!

Tue, 08 Sep 2015 13:53:25 GMT

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.

Re: Line chart help with date needed!

avinashelite — Wed, 09 Sep 2015 06:11:20 GMT

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

Re: Line chart help with date needed!

Wed, 09 Sep 2015 07:52:55 GMT

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.

Re: Line chart help with date needed!

avinashelite — Wed, 09 Sep 2015 08:05:13 GMT

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

Re: Line chart help with date needed!

Wed, 09 Sep 2015 08:38:45 GMT

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?

Re: Line chart help with date needed!

avinashelite — Wed, 09 Sep 2015 09:31:01 GMT

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

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

Re: Line chart help with date needed!

MarcoWedel — Wed, 09 Sep 2015 10:46:35 GMT

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

Re: Line chart help with date needed!

Mon, 21 Sep 2015 11:23:58 GMT

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

Re: Line chart help with date needed!

Mon, 21 Sep 2015 11:49:34 GMT

Hi Avinash,

I did this but I get an error - can you see the error?

I am trying to change from Q1-2010,.... to 2010-Q1... I believe that would fix the order.

NewTable:

NoConcatenate

LOAD [Country],

Right(Quarter_Year,4) as Year,

Left(Quarter_Year,2) as Quarter,

Year&'-'&Quarter as New_Quarter_Year

Labour_Rate, Quarter_Year

Resident Table;

DROP Table Table;

Re: Line chart help with date needed!

Mon, 21 Sep 2015 11:50:15 GMT

Do you aslso understand what Marco is doing here:

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;

Re: Line chart help with date needed!

MarcoWedel — Mon, 28 Sep 2015 23:50:31 GMT

Hi,

please feel free to ask your question.

regards

Marco