1 Reply Latest reply: Mar 11, 2015 6:45 AM by Gysbert Wassenaar RSS

    Sort two dimensions based on total from an expression column within the range of the dimension

      Hello,

       

      We have the following situation in a straight table:

      3 dimensions ==> BRANDS/GROUPS/Product

      1 Expression ==> Sold Incl VAT

       

      We have to define a sort on the dimensions BRANDS and GROUPS, based on the total sum of Sold Incl VAT inside that brand/group (descending order).

       

      Our result should look like this:

      Everything from Brand 1 should be first because the total of brand 1 (9.000,-) is higher than the total on brand 2 (8.000,-).

      Inside brand 1 everything from group 1 should come first because the total of group 1 (5.000,-) is higher than that of group 2 (4.000,-).

      Inside brand 2 everything from group 1 should come first because the total of group 1 insidie brand 2 (5.000,-) is higher than the total of group 2 inside brand 2 (3.000,-).

       

      We tried a few different solutions:

      - we started by first disabling all kinds of sorting on the table.

      - first we moved the Sold Incl Vat column up in the sort hierarchy (we tried: before BRANDS, before GROUPS, after GROUPS, etc...)

      - then we tried to add a sorting on both brands/groups with an expression equal to the expression used in Sold Incl VAT

       

      Nothing seems to be correct. There still seems to be a kind of randomness in the sorting.

       

      What are we doing wrong here? Thx in advance!

       

      BRANDS (D)

      GROUPS (D)Product (D)Sold Incl VAT (E)
      Brand1Group1prod15000
      Brand1Group2prod24000
      Brand2Group1prod11000
      Brand2Group1prod1b1000
      Brand2Group1prod1c1000
      Brand2Group1prod1d1000
      Brand2Group1prod1e1000
      Brand2Group2prod21000
      Brand2

      Group2

      prod2b1000
      Brand2Group2prod2c1000