Dimension is lost when adding a second expression to the chart

[#1]

Hi

I have a line chart where I am graphing avg(latency) vs time for multiple hostnames. In the dimension tab, under 'used dimensions' i have 'createdAt' followed by 'hostname'.  In expressions, i have only 'Avg(latency)'. This produces a nice graph with 9 lines one for each hostname. See 'good graph.png' attachement.

However, when i add a second  expression 'Max(latency)', i only see 2 lines - one for max and one for average; I expect to see 18 lines - avg and max for each host.  See 'bad graph.png' attachment.

It appears it's ignoring my 'hostnames' dimension. What happened? Is this a setting I can change?

Thanks!

[johnw]

In a line chart, QlikView displays multiple dimensions or multiple expressions, but not both.  I don't know of a setting to change this.  I consider 18 lines too many for a person to process, but if you really want to go down that road, you can fake something up.  The attached example does so by combining 'Avg' and 'Max' with the dimension to create a new dimension, and then checking whether this is an average or max dimension when calculating the value.  In other words, it "moves" the two expressions into the dimension so that we only have one expression.

Script:

Data:
LOAD
recno() as ID
,ceil(rand()*5) as Dim1
,chr(ord('A')+floor(rand()*3)) as Dim2
,ceil(rand()*100) as Value
AUTOGENERATE 100
;
Exp:
LOAD *
,Dim2 & ' ' & subfield('Avg,Max',',') as Dim2Exp
;
LOAD text(fieldvalue('Dim2',recno())) as Dim2
AUTOGENERATE fieldvaluecount('Dim2')
;

Chart:

Dimension 1 = Dim1
Dimension 2 = Dim2Exp
Expression  = if(right(Dim2Exp,3)='Avg',avg(Value),max(Value))

[Anonymous]

Im not if this will work. Try to ad a 3rd expression "=1" and then tick it invisble. Henrik

[#4]

Thanks for your prompt response.

However, I think there are two issues:

- this is a rather arbitrary limitation. While showing many lines might not make sense for financial data, it does make sense for performance monitoring, e.g. to see if one instance of the application is not performing like the rest.

- the UI should notify of the limitation instead of silently hiding the extra dimension. I spend quite a bit of time trying to figure this out before resorting to the forums .

Thanks again!

[johnw]

You're welcome, and believe me, I agree with both your points.  It IS an arbitrary limitation that should NOT be in the product.  I believe we should be able to use as many dimensions and expressions we want, and specify which ones show up how, and if we want 100,000 lines in our chart that's OUR business.  Besides, your second dimension might have only two values, and you might have only two expressions, and that's only four lines.  Anyone can understand a mere four lines.  So it's a ridiculous limitation all around.  And yes, since the product IS limited, it should absolutely tell you "you can't do that" or prevent you from doing it.  What it shouldn't do is what it does - let you do it and then silently simply NOT WORK.

I do like QlikView a lot.  But there are a lot of seemingly arbitrary and inconsistent limitations that I think just shouldn't be there, and they drive me nuts when I run into them, even if I have enough experience by now to quickly come up with workarounds (however nasty a hack they might be, such as the one here).

[#6]

Dear John,

I am at the moment digesting your solution, and I must admit it is a reallty nice trick.

Indeed, I am faced with a similar problem as antonmos, but possibly slightly more complex.

I also have a line chart, but I want to graph planned vs. actual orders, per each week.

My intention is to leave a detail per sales group and see all this at once.

By now, I just manage to either see the cumulative planned vs the cumulative actual orders; or the planned (or actual) per sales group.

Therefore the two expressions need to refer to actual data that are stored, and cannot be calculated in-script by means of Avg or Max, like in the solution suggested.

Do you think this is achievable?

Many thanks!

Erik