Combining two line charts into one

[#1]

Hi,

I want to create one line chart with five lines (of five countries) showing the number of wins on the Y-axis and the last three years on the X-axis.

The first line should show the results of the country which I have selected.

The four other lines should show the results of the best four countries (countries with most wins) which are shown if I enter a set analysis, enter 4 as Max Visible Number and enter a correct sorting.

In example file the two different charts are shown, how can I combine those two in one?

Any help is much appreciated.

Chris

[Anonymous]

HI Chris,

An easy (but not so nice) workaround to do this is just put the 2 charts on top of each other and make the top one 100% transparant (color tab)

But there also must be a way to combine this. I will look into this later.

[Anonymous]

Hi Chris

There is an option to add a ference line (Properties >> Presentation)
I think you should be able to an expresion there which returns a line in the charts.

Let me know if it works ok?

[Anonymous]

Sorry that is not right, this only returns 1 line....

[#5]

Thanks Dennis for looking into this.

I have now added a third chart where the top4 countries and one selected country (Netherlands) are shown.

This done by the following statement

=ONLY({$+<Country={"China","USA", "England","Russia"}>}NumberOfWins)

So the name of the countries are now hard coded.

But the four countries should be dynamic, users will be able to filter on sports, year, etc...

I can't find the correct (set analysis) statement to get the dynamic top 4 countries + the selected country?

Chris

[Anonymous]

Hi Chris,

I think you can use the RANK function for this
Maybe John's answer can help you.

Take a look here: http://community.qlik.com/forums/t/31136.aspx

Good luck.

[#7]

I did this by changing your Expression to :

ONLY({$+<
Country={"$(=concat(aggr(if(rank(aggr(
sum(NumberOfWins)
, Country)
) <=4, Country),Country), ','))"}>}NumberOfWins)

A bit of explanation: in only statement I choose Countries that rank as first 4 and separate them with ',' char.

regards,

Miha

[#8]

Hi Miha,

Thanks, concat definitely looks like the solution. The lecture of John W was perfect and the concat will solve the (unique) problem in my situation.
But unfortunately the expression doesn't work in my document.

If I select Netherlands, only line of Netherlands is shown and if no country is selected all six countries are listed.

So please let me know if I did something wrong.

I tried something myself but it doesnt work in the chart I need to produce. I added another aggr to your concat expression like

aggr(concat(aggr(if(rank(aggr(sum(NumberOfWins),Country)) <=4, Country),Country), ','),Year)

see table in attached in attached example:

it gives the four countries in correct concat, but this unfortunately does not work in the expression in the chart I need

Chris

[#9]

I have found the solution.

It is a combination of the rank function as suggested by Dennis and using the "plus" to combine the top 4 countries and the selected country via set analysis.

sum({<Country={"=rank(sum({<Country=>}NumberOfWins), Country)<5"}>+<COUNTRY={"=GetFieldSelections(Country)"}>}NumberOfWins)

this line of code only works when the COUNTRY is in capitals after the +< ??

Example is attached.

[johnw · Accepted solution]

If you select nothing, what do you want to show? Right now, it doesn't show USA, which is one of the two top countries. That's easily fixed by removing the restriction on the number of shown values of country. But at that point, it displays all countries. Wouldn't you want it to display ONLY the top 4 if no countries are selected?

Honestly, I don't see what's going on with the capitalized COUNTRY. That's not even a field. You can replace it with "X", and you seem to get the same result. I have no idea what sort of set is being generated by QlikView when you ask for a nonexistant field, and no idea how that's making your expression almost work. I'd love to hear someone explain it to me.

In any case, if I've understood what you want, I can get it like this:

if(getselectedcount("Country")
,sum({<Country={'$(=getfieldselections("Country", chr(39) & ',' & chr(39)))'}>
+<Country={"=rank(sum({<Country=>} NumberOfWins), Country)<5"}>} NumberOfWins)
,sum({<Country={"=rank(sum( NumberOfWins), Country)<5"}>} NumberOfWins))

It could probably be simplified, but it made sense to me to just split it apart by whether or not any countries are selected.

See attached.