Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Keeping comparable data in bar chart

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)

CountryProductAverage Markup
UKA£0.20
UKB£0.40
UKC£0.60
UKD£0.10
SpainA£0.80
SpainB£0.50
SpainC£0.30
SpainD£0.25
PolandA£1.00
PolandB£1.20
PolandC£0.50
PolandD£0.35

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

T

1 Solution

Accepted Solutions
giakoum
Honored Contributor II

Re: Keeping comparable data in bar chart

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.

6 Replies
giakoum
Honored Contributor II

Re: Keeping comparable data in bar chart

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.

Not applicable

Re: Keeping comparable data in bar chart

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:

CountryCompanyProductAverage Markup
UK1A£0.20
UK2B£0.40
UK1C£0.60
UK1D£0.10
Spain2A£0.80
Spain1B£0.50
Spain3C£0.30
Spain1D£0.25
Poland1A£1.00
Poland2B£1.20
Poland3C£0.50
Poland2D£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?


giakoum
Honored Contributor II

Re: Keeping comparable data in bar chart

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!

Not applicable

Re: Keeping comparable data in bar chart

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.

giakoum
Honored Contributor II

Re: Keeping comparable data in bar chart

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.

Not applicable

Re: Keeping comparable data in bar chart

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

Big help

Community Browser