strange behaviour with aggr(), possibly a bug?

[ekech_infomotio]

Hi,

I'm struggling with a real strange behaviour in trying to set up an expression with aggr().

I have a pivot-chart with two dimensions, first dimension represents a time-key (APFA_ZDIM_ID), second dimension is a product-key (%FODI_KEY). (yes, I know you normally won't use keys as dimensions, but in this example I have only isolated the problem to a single table, hence the key). Now I have to show the changes over time in percent of a single value, starting from the beginning of the current time-selection.

I expected to use this expression as basis:

aggr(top(sum(AS_PERFO_sA)), %FODI_KEY, APFA_ZDIM_ID)

so that the final expression would be:

sum(AS_PERFO_sA)

/

aggr(top(sum(AS_PERFO_sA)), %FODI_KEY, APFA_ZDIM_ID)

Discussing with my colleagues makes me think, that the expression is logically correct but I get a very strange and unpredictable results.

For some selections in APFA_ZDIM_ID I get correct values, others do deliver completely wrong numbers.

Example QVW is appended, also a little Excel-Sheet to show what I mean 😉

Can anyone confirm this behaviour as bug or do I simply have a wrong expression?

regards,

Edgar

[swuehl · Accepted solution]

To get your results, you assume that the APFA_ZDIM_ID or time-key is sorted chronologically.

For an aggr() function, that's not the case, the aggr() function dimension values will just come in in load order (and for your time-key, load order is not chronological) and there is no way to explicitely tell QV the sort order for aggr() dimensions.

To get correct results, I believe you need to get your time-key load order in chronological order, e.g. by preloading the field ordered asc.

[ekech_infomotio]

Hi Stefan,

thanks a lot - this helped, although I think, this behaviour is crap whenever I can't manipulate my load order. Another question, another load order?

regards,

Edgar

[swuehl]

Edgar,

I absolutely agree.

I've also struggled several times with the specifics of the aggr() function so far. Maybe we should create an idea (I hope there is already one regarding the aggr() dimensions sort order, but I am not able to search the ideas in a appropriate manner)?

The main issue arise from the use of chart inter record functions like top() inside the aggr() function, so if we get around using these, we get around the issue.

For example, you should be able to use something like

=sum(total<%FODI_KEY>

aggr(if(APFA_ZDIM_ID=min(total APFA_ZDIM_ID),sum(AS_PERFO_sA)),%FODI_KEY, APFA_ZDIM_ID))

instead of your original expression, without having the need to care for the load order (and probably there is even a more elegant expression, which I miss see right now).

Regards,

Stefan

[s_uhlig]

Yes, please vote for the idea

http://community.qlik.com/ideas/1391#comment-1873

Regards,

Sven

[ekech_infomotio]

@S.Uhlig: Done 😉 Would be nice to get more control over such important functions.

regards,

Edgar