topic Using two dimensions and two expressions on a line chart in QlikView

Using two dimensions and two expressions on a line chart

Mon, 09 Feb 2015 20:26:11 GMT

Hello all!

I have read multiple posts with multiple answers on whether one can successfully have two dimensions with two expressions on the same line chart. In my example, I do not believe that I can do the above and will need to resort to creating one dimension; however, I am having some difficulty.

My sample data consists of the following:

ServiceRecordNumber	PartNumber	ShipYear	ServiceRecordYear
1	A	2009	2010
2	A	2009	2011
3	A	2010	2012
4	A	2011	2014
5	A	2012	2012

I would like to be able to plot both the [ShipYear] and [ServiceRecordYear] on the same dimension (i..e x-axis) for all the parts listed above. If I chart each field separately; meaning I create one chart with [ShipYear] as the dimension and

Count({$<[ShipYear]={2010, 2011, 2012}>}[PartNumber])

as the expression, the chart is fine. However, if I use both [ShipYear] and [ServiceRecordYear] as the dimensions and with similar expressions as above, the results are not correct.

I currently have all of the above information in one table, i.e.

Load [ServiceRecordNumber],

[PartNumber],

[ShipYear],

[ServiceRecordYear];

from XYZ

I do not believe that I can use concatenation since all of my data is in one table. The real data is stored in an Access database and is set up similarly as the above table.

I have seen where I may need to use either a data island "master calendar" to handle this situation; however, this is where I ran in to difficulty.

I created a table with a named field [XaxisYear] that holds generic years 1995-2015, and it is not linked, it is a "data island". I used [XaxisYear] as the single dimension and then created similar counting expressions as above using [ShipYear] and [ServiceRecordYear]. I am not sure what QlikView counted, but the results are not correct.

In the meantime, I am going to create separate charts since that does work and I can continue onwards. Being able to chart both [ShipYear] and [ServiceRecordYear] on the same chart will be the enhancement.

As always, any advice is greatly appreciated. 😃

-Jill

Re: Using two dimensions and two expressions on a line chart

Anonymous — Mon, 09 Feb 2015 20:47:33 GMT

Here is one way of doing it by using one "Year"-field and splitting up the data i Source instead.

// First we load the data

TempFacts:

LOAD * INLINE

[

ServiceRecordNumber, PartNumber, ShipYear, ServiceRecordYear

1, A, 2009, 2010

2, A, 2009, 2011

3, A, 2010, 2012

4, A, 2011, 2014

5, A, 2012, 2012

];

// Then we take the "Shipping Data"

Facts:

Load

ServiceRecordNumber,

PartNumber,

ShipYear as Year,

'Shipping Data' AS Source

Resident TempFacts;

//Then we take the "Service Data"

Concatenate(Facts)

Load

ServiceRecordNumber,

PartNumber,

ServiceRecordYear as Year,

'Service Data' AS Source

Resident TempFacts;

DROP TABLE TempFacts;

Once loaded we can present the following two values on the same Year-axis:

COUNT({<Source={'Service Data'}>} DISTINCT PartNumber)

COUNT({<Source={'Shipping Data'}>} DISTINCT PartNumber)

The alternative (maybe more correct) approach is to follow the Canonical Date concept:

Canonical Date

Kind regards

Niklas

Re: Using two dimensions and two expressions on a line chart

Mon, 09 Feb 2015 20:59:43 GMT

Thank you Niklas for the speedy response. I will try your method and also the Canonical Date method as well.