Why isn't the column() chart function working inside a concat() function ????

[diagonjope]

Hi there!

While doing some troubleshooting for another app, I realized that the column() function does not seem to work when used inside a concat() function.  Take a look at this image from a simpler test app:

The last column of the chart checks each value of the Value field against the value of column(1) (the avg), and depending on the value it outputs the concatenation of Value&'>='&column(1) or Value&'<'&column(1).  It turns out that according to the if(), the value of column(1) is greater than any of the values in the Value column, but it is supposed to be the avg() of these values, and the actual value of column(1) is never displayed.

Does anyone know why this is happening?

I am attaching the QVF of the test app, in case anyone wants to give it a shot.

Cheers,

++José

[Or]

Concat() is an aggregation function. Avg() is an aggregation function. You can't nest aggregation functions without using Aggr(), so this can't work. At least, I think that's the problem without having had a play with the actual app.

[diagonjope]

Thank you Or for your reply. 

I agree with your reasoning, but using aggr() is not working either.  Take a look at the image below.

BTW, while I have your attention, I got to explore this after trying to count the number of values of Value that are below and above the corresponding mean for each Key, not the overall mean.  After trying with set analysis and TOTAL<Key> qualifiers, I couldn't get it to work.  That's when I decided to try to use column(), but it didn't work either, which lead to display what value was column(1) returning.  In the image above, notice that in column(5) (second column from the right) for Key = 'B', Sense indicates that it has two 2 values below B's mean (47), when in fact it has 3 (21, 34, 46).   It seems the calculation is using the overall mean (41.625) instead of the mean per value of Key  .  Can you also share your thoughts about this?

Cheers,

++José

[Or]

Sorry, I'm not enough of an expert on the internals of the Qlik engine to tell you how it handles this sort of situation where you're making a reference to another column from within an aggregation. This is where I typically hope someone like  @hic can help.

[hic]

Because they operate on different domains.

The chart has Cardinality=2, i.e. two values in the dimension. Two rows in the chart. This means that Avg(Value) is calculated twice, and this calculation can be accessed by other expressions that operate on the same domain.

But this is different from what goes on INSIDE an aggregation function. An expression inside an aggregation function is evaluated on row-level in the raw data, which in this case has Cardinality=8 (but could have been several millions). And a row in raw data can be associated to BOTH dimensional values in the chart (if you have a many-to-many relationship in the data model). So the engine cannot associate an individual row in data with an individual row in the chart. 

To make matters worse: An Aggr() call will mean a third domain, with possibly a  third Cardinality. And you have the same problem here: An individual row in the Aggr() can be associated with multiple rows in both the chart and in the raw data. 

Usually (but not always) you can get around such problems by using total <field> inside the chart or the Aggr().

[diagonjope]

Thank you @Or for invoking the assitance of @hic, and thank you Henric for your detailed explanation.

I finally "solved" the problem over the weekend, but the way I did it seems convoluted to me, giving the simplicity that I have come to expect from the Qlik calculation engine.  I added a unique record Id field (Rec) to each record and then used the following expression to count the number of values below the mean for each value of Key : count({$<Rec={"=aggr(NODISTINCT avg(Value),Key) > Value"}>} Value)

                                                                                                                                                                                                                                 

I still don't understand why the NODISTINCT makes a difference in this case, given the values in test data set, but the expression doesn't produce the expected results without it.

I also don't understand why the following expression does not produce the same results: 

count({$<Rec={"=avg(TOTAL<Key> Value) > Value"}>} Value)

Any thoughts about why this is happening?

Cheers,

++José

[hic]

The expression

count({$<Rec={"=avg(TOTAL<Key> Value) > Value"}>} Value)

will not do what you want since

"=avg(TOTAL<Key> Value) > Value"

is evaluated once per distinct value of "Rec". This domain is not aware of the dimensions in the chart, so the "TOTAL <Key>" has no meaning.

[diagonjope]

Hi Henric,

Thank you for your response.  Apologies for insisting on this issue, but I just want to understand what's going on underneath.  I changed Rec for Value in the expression (same result), so that we can use a field that is one of the dimensions of the chart below.

Why is the engine returning a value of 0 for count({$<Value={"=avg(TOTAL<Key> Value) > Value"}>} Value) when [Rec]=6?   My expectation was that the engine was going to use the corresponding avg of [Value] for each value of [Key] to make the comparison for each record, but it seems that in this column the engine is using the overall avg of all values of [Value] (41.625).  

In conclusion: to achieve the result I need, is there a simpler approach that you can suggest other than using the expression with the aggr(NONDISTINCT) within the set expression?  It seems too complex to me.  How was something like this done in QV when Qlik didn't have set analysis capabilities?

Cheers,

++José