Help with combo chart

[#1]

Hi All,

I am trying to create a combo chart similar to the attached file.

I have my data something like .   country/ Month / year/ passed/failed/total.

I am trying to create a combo graph . where I  display total as bars and  pass rate as line [ BY COUNTRY].

I tried to achieve the lines and bars by country , by month using aggr functions as below, but this   doesnt work. Any pointers on how I can get this done.

My dimensions are:  YEAR / MONTH

Expressions: sum(aggr(sum(total),country))   -- for  total by country

                     sum(aggr(sum(passed)/sum(total),country)) -- pass rate by country

I am trying to achieve something like below.

[#2]

To add to that .

Just realized i was aggregating only on country level . I have added additional aggregation levels.

aggr(sum(total),year,month,country)

however I still didnt get what I wanto as in the pic I attached to the issue.

Thanks,

kranti

I just saw there was similar question posted in community .

http://community.qlik.com/message/181373#181373

I  have attached a sample file I am working on. May be you can help me with that .Hope to see what I am expecting to build here

Thanks,

kranti

[#3]

Hi kranti, interesting task!

How many countries do you have? If they are small, and finite you could write each bar and line as an expression that filters the data for that country using set analysis, if that fits your model.

Regards,

Erica

[subin6600]

I Have made some changes and attached the file.

Hope it helps your problem.

BR/Subin

[#5]

Hi Erica, Thanks for your response.

Well I have ~ 20 countries. I am fine with it as long as I can see the output as I expect it to be.

can you please point me in the right direction " using set analysis"

Thanks,

kranti

[#6]

hi subin6600,

Thanks for response.  I only have a personal edition of QV so I cannot view your file. Could you please describe the changes you made to the document.

sorry for the additional trouble.

Thanks,

Kranti

[#7 · Accepted solution]

hi kkbanala

you need to insert the set analysis {<[Country]={"[relevant country]"}>} to filter for each country into the aggregate functions

EG: The syntax for each set of lines / bars for France would be something like

=sum({<[Country]={"France"}>} Total)

If you tick the above as "bar" and then a similar expression as line, you will get the above as bars, layed out over the dimensinos and then the other expression as a line.

Hope this helps,

Regards,

Erica

[#8]

Thanks Erica. That worked.