Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner - Master II
Partner - Master II

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.

View solution in original post

6 Replies
giakoum
Partner - Master II
Partner - Master II

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
Author

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
Partner - Master II
Partner - Master II

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
Author

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
Partner - Master II
Partner - Master II

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
Author

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

Big help