topic Re: Keeping comparable data in bar chart in QlikView

Keeping comparable data in bar chart

Tue, 14 Aug 2012 11:04:14 GMT

I have the following data that I have turned into a bar chart (Dimension=Product and Expression=Average Markup). I then have a list box for the country so that the graph is dynamic.

What I would like to do is design the bar chart so that the UK bars alway remaim on the chart as a comparitor and can be seen behind the selected country from the list box (in this case Spain or Ukraine)

Country	Product	Average Markup
UK	A	£0.20
UK	B	£0.40
UK	C	£0.60
UK	D	£0.10
Spain	A	£0.80
Spain	B	£0.50
Spain	C	£0.30
Spain	D	£0.25
Poland	A	£1.00
Poland	B	£1.20
Poland	C	£0.50
Poland	D	£0.35

I suspect this means adding a new expression with a bit of set analysis but I am stuck, any help much appreciated.

Re: Keeping comparable data in bar chart

giakoum — Tue, 14 Aug 2012 11:10:09 GMT

What about a new expression like this :

avg({$<Country]={"UK"}>} [Average Markup])

Or you may select UK, right click on the chart and select Set Reference, and then continue by selecting another country.

Re: Keeping comparable data in bar chart

Tue, 14 Aug 2012 12:39:36 GMT

Thanks that works wonders! I think you missed a square braket:

avg({$<[Country]={"UK"}>} [Average Markup])

Sorry but I have a supplementary question:

I want to add another variable (company in the table below) and therefore another listbox:

Country	Company	Product	Average Markup
UK	1	A	£0.20
UK	2	B	£0.40
UK	1	C	£0.60
UK	1	D	£0.10
Spain	2	A	£0.80
Spain	1	B	£0.50
Spain	3	C	£0.30
Spain	1	D	£0.25
Poland	1	A	£1.00
Poland	2	B	£1.20
Poland	3	C	£0.50
Poland	2	D	£0.35

I now have two list boxes (country and company). The chart's dimension remains product and the expression uses the Average Markup field.

Is it possible for the UK data to remain on the chart and update automatically depending on the company is chosen in the list box?

Re: Keeping comparable data in bar chart

giakoum — Tue, 14 Aug 2012 12:48:33 GMT

Not sure if I understood well, but something like this for example :

if(Company='1', avg({$<[Country]={"UK"}>} [Average Markup]), avg([Average Markup]))

or do you mean having UK data regardless of company :

avg({$<[Country]={"UK"}, [Company]=>} [Average Markup])

Sorry for missing that bracket!

Re: Keeping comparable data in bar chart

Tue, 14 Aug 2012 13:11:36 GMT

Thanks, I have tried these and they are not quite returning what I need.

If someone selects Spain and Company 1 I need the graph to show data for Spain and Company 1 alongside data for the UK and company 1. If they select Spain and company 2 I need the comparable data to be UK and Company 2.

Re: Keeping comparable data in bar chart

giakoum — Tue, 14 Aug 2012 13:15:08 GMT

Then this should be sufficient :

For UK expression : avg({$<Country]={"UK"}>} [Average Markup])

For any other country : avg([Average Markup])

Company selection should work in both cases, as the $ identifier is used.

Re: Keeping comparable data in bar chart

Tue, 14 Aug 2012 15:27:20 GMT

Thank you very much - I have added the [ bracket to the UK expression and it works a treat!

Big help