topic Re: Issue with Line Chart in QlikView

Issue with Line Chart

Fri, 01 May 2015 19:00:00 GMT

Hi,

I have a requirement where need to create line chart with 2 dimensions and 2 metrics.

Where my first dimension need to be on x-axis ,and my first metric line need to be split based on 2nd dimension and 2nd metric need to be display at 1st dimension level.

Means one metric need to be show the lines in detail at 2nd dimension level and other metric need to be display at aggregate level(1st dimension level) in the same chart.

Ex;

Month	Country	Sales	Orders
Jan-15	India	200	400
Feb-15	USA	300	500
Mar-15	China	500	600

With the above example in line Chart the Sales line need to be display at Country level( for each country one line) and Orders need to be display at Month level(one line) .

Is it possible in Qlikview to show lines of one metric at detail level and other metric at aggregate level in charts.

Thanks,

Shekar.

Re: Issue with Line Chart

petter — Fri, 01 May 2015 19:12:29 GMT

Could you add some more to the example data and possible use Excel to show us the chart how it should look like?

Re: Issue with Line Chart

Fri, 01 May 2015 19:41:28 GMT

Hi,

Here is more sample data:

Month	Country	Sales	Orders
Jan-15	USA	300	600
Jan-15	China	600	800
Jan-15	India	200	400
Jan-15	Total	1100	1800
Feb-15	USA	300	400
Feb-15	China	200	300
Feb-15	India	300	500
Feb-15	Total	800	1200
Mar-15	USA	500	600
Mar-15	China	400	500
Mar-15	India	300	400
15-Mar	Total	1200	1500

On X-axis Month and Y- axis Sales and Orders need to be get display. Ex: for Jan-15 Sales as to display in 3 lines (300,600,200)(one for each country), Orders need to be display in one line as 1800(total).

Hope you understood my requirement.

Thanks,

Shekar.

Re: Issue with Line Chart

petter — Fri, 01 May 2015 19:51:07 GMT

yes now it is much easier to understand - thank you

Re: Issue with Line Chart

Fri, 01 May 2015 20:36:20 GMT

Hi,

I got the clue.

Where we can use expression for each country by using Only & Set analysis functions. So that we can get one metric at detail level and other metric at aggregate level.

USA Sales Exp:Only({<Country={'USA'}>} Sales)

India Sales Exp:Only({<Country={'India'}>} Sales)

China Sales Exp:Only({<Country={'China'}>} Sales)

Total Orders Exp:Sum(Orders)

Thanks,

Shekar.

Re: Issue with Line Chart

petter — Fri, 01 May 2015 21:05:47 GMT

Exactly - You don't have to have all dimensions in the Dimension tab you can also

pick out your values with Set Expressions by referring to dimensions in the expression

as you noted. However I wouldn't go for the Only() aggregate function unless you always

know that you will get one value per dimension on the x-axis. Sum is still good as aggregate

here.

So:

Sum( { < Country={'China'} > Sales )

Sum( { < Country={'India'} > Sales )

Sum( { < Country={'USA'} > Sales )

Sum( { < Country={'TOTAL'} > Orders )

But actually you should drop all the rows with TOTAL in your data model and rather have:

Sum( { < Country={'China'} > Sales )

Sum( { < Country={'India'} > Sales )

Sum( { < Country={'USA'} > Sales )

Sum( Orders )

Sum ( Sales )

Which is much safer and with less redundancy and little room for mistakes...