How to use 2 dimensions and above() inside a line/combo chart?

[Anonymous]

Hi folks,

I have a chart I've created that uses two dimensions, one of these I limit down to an individual value based on a variable. My main expression uses a rank and aggr combo to get the rank of the second dimension within the first dimension. The result gives me the desired chart that shows the rank of the individual value over time.

I know want to add an expression to the chart that highlights runs of 2 or more data points where the rank has improved. I've attempted this using the above function and have had success with this method in other charts, however the second dimension is adding some complexity. I can get the desired result when using pivot or straight tables, but when I turn these into combo or line charts the expressions start failing.

I've attached an application with the data and included some notes on what I'm trying to achieve and what isn't working.

I've had a look around the forums and there are similar issues that have been solved but none of these have worked and nor do they use an aggregated rank expression like this.

All help is greatly appreciated!

G

[Oleg_Troyansky]

Hi Graham,

I admit, that's quite a lot of logic to go through and troubleshoot... Let me give you some pointers that could possibly help you find your solution:

- I don't believe that above() and below() can be used in a nested formula, and you have plenty of those. I'm not too sure what are you trying to calculate with that, but I'm sure there is a simpler way of achieving the same.

- In charts with multiple dimensions, functions like rank(), above() and below() operate within each segment - in other words, they reset when the main dimension's value changes. In order to use these functions across all dimensions, you can add the TOTAL prefix.

- In many situations, using AGGR() is a good way of breaking out of the restrictions of your chart dimensions. Again, there might be a need to use TOTAL here and there.

If you have smaller and more pointed questions, I could possibly help...

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!