12 Replies Latest reply: Feb 11, 2011 1:56 PM by John Witherspoon RSS

    Combining two line charts into one

       

      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

        • Combining two line charts into one
          Dennis Hoogenboom

          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.

          • Combining two line charts into one
            Dennis Hoogenboom

            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?

             

              • Combining two line charts into one
                Dennis Hoogenboom

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

                  • Combining two line charts into one

                     

                    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

                     

                     



                      • Combining two line charts into one
                        Dennis Hoogenboom

                        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.

                          • Combining two line charts into one

                            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

                              • Combining two line charts into one

                                 

                                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

                                 

                                 



                                  • Combining two line charts into one

                                    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.

                                     

                                      • Combining two line charts into one
                                        John Witherspoon

                                        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.

                                          • Combining two line charts into one

                                            Thanks John,

                                            Your suggestion is exactly what I need.

                                            Chris

                                             

                                            On the subject of my expression and the nonexistant field:

                                            I have stripped the code and the code below is also working and gives you the same result as in my previous (almost working) expression

                                             


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


                                            The dot can also be replace by f.e.: >

                                            ??

                                              • Combining two line charts into one
                                                John Witherspoon

                                                Maybe any broken set functions as the set of all selections? In other words, maybe a broken set functions like $? I can see how if you did the union of $ and the ranked countries, you'd get the behavior you're seeing. Yep, $ seems to do that, and it make sense with $. So I bet broken sets are functioning like $.

                                                I suppose it makes sense in a way. It's not possible to select a value in a field that doesn't exist, so doing that means doing nothing, which means the set is $. Not sure why something as simple as a dot would still do that, rather than returning a syntax error. For that matter, I'm not sure why referencing nonexistent fields doesn't return a syntax error, and therefore null. But I guess it's not a big deal, just strange.

                                                  • Combining two line charts into one
                                                    John Witherspoon

                                                    Hmmm, I thought I could simplify to this, but I guess it's confused and can't see that I'm doing dollar sign expansion instead of using the $ set. Thanks for using the same symbols to mean different things, QlikView. Helpful.

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

                                                    Might be something I did wrong, of course, but it would be nice if I could eliminate "QlikView is misinterpreting my symbol" as a possible cause.

                                                    Anway, at least we can simplify this much:

                                                    if(getselectedcount("Country")
                                                    ,sum({<Country={"=rank(sum({<Country=>} NumberOfWins), Country)<5"}>+$} NumberOfWins)
                                                    ,sum({<Country={"=rank(sum( NumberOfWins), Country)<5"}> } NumberOfWins))

                                                    There has to be something simpler, though, with all of that code the same between the two lines.