How to use set analysis and alternate states in combo chart?

[#1]

Dear all,

I have a barchart that shows several amounts for each company over the years. It works well, but now I want to add the values of a benchmark over the years in order to compare the values of the bars with the values of the benchmark. I've tried a lot, but is not working.. I think the best way to show my problem is to make an simplified example in Excel and Qlikview, so I did and I attached it.

As you can see, I loaded the data into a cross table. I have several listboxes:

- Companies (A, B, C)

- Year (2011, 2012, 2013)

- Fields (Sales, Equity, Liabilities) in alternate state alt1 (for several reasons in my original project)

The combochart has two dimensions: Year, Company

And one working expression:

Sum({alt1<Year =$::Year, Companies =$::Companies>} Data)

Now I want to add an expression which adds a line to the graph, according to the benchmark values (also shown in the excel file: just an average for each year).

I tried this and many other things, but it does not work at all.

sum({alt1< Year =$::Year, Companies =$::Companies>} Data) / 3 //number of companies

I'm new to QV, so I hope it is not a stupid question and I hope that my question is clear enough.

Can anyone help me? (see the attached files)

Many thanks in advance!

[Gysbert_Wassenaar · Accepted solution]

To be honest it's not clear to me what you're after. But in that combo chart you're using two dimensions and more than one expression. That will not work. Try removing the Year dimension from the chart. And perhaps you want this expression for the benchmark: =sum({alt1< Year =$::Year, Companies =$::Companies>} total <Year> Data)

[#3]

Thanks, it works! I was too focused on using two dimensions. When I select all possible years and I select more than one company, I prefer not to have the values summed over the years. But do I understand well that it is not possible to show the benchmark seperated for each year and each company, when more than one year is selected and more than one company is selected?

[Gysbert_Wassenaar]

But do I understand well that it is not possible to show the benchmark seperated for each year and each company, when more than one year is selected and more than one company is selected?

No idea. I don't understand what your benchmark value is supposed to be. Perhaps you can write out an example.

[#5]

Sorry that I'm not clear enough. I just found the solution by indeed adding 'total <Year>' to the Benchmark-expression, but keeping the two dimensions Year and Company. Now, I can compare the values of each company per year with the benchmark values per year, and that is what I want to have.

Thanks again!

[#6]

I had the same problem. I could fix by removing one of the dimensions. It was not needed to show the correct diagram.