2 Replies Latest reply: Sep 9, 2015 5:33 PM by Ruud Cruchten RSS

    Sort order first on ... then on ...

    Ruud Cruchten

      Hi,

       

      I have a bar chart which shows the development of skills per employee. Some explanation: Employees get a skill rating between 1 and 4 for a current, short term and long term level. The bar chart shows the growth of the employee over time. It's a stacked bar chart with three expressions:

      1. current level

      2. short term level - current level

      3. long term level - short term level

      See picture below (ST = short term LT= Long term)

       

      I want to sort the bar chart in this manner:

      first on current level

      then on growth short term

      then on growth long term

       

      The first bars should only show people who don't have to learn.

      the next bars should show people who only have to learn on a short term

      then the  bars should show people who have to learn on the short term and long term

      then the bars should show people who only have to learn on the long term.

       

      To make more complicated (and more readable) the current level is leading, first the growth of all the current 1, then current 2, etc..

       

      I've some sort expressions but i can't get it to work. It should look like this:

       

      This one happens to be correct, no idea why.

      My current sort expression is:

       

      [Current level]&IF(([Target level long term]-[Target level short term])=0,([Target level short term]-[Current level]),

                          IF(([Target level long term]-[Target level short term])=([Target level short term]-[Current level]), [Target level short term],

                              IF(([Target level long term]-[Target level short term])>([Target level short term]-[Current level]), [Target level long term])))

       

      Thanks for your help!