Sorting a calculated dimension

[Anonymous]

Hi all,

i'm trying to sort a calculated dimension using the expression bellow:

=Match(if(InventoryFrozenDispDays>='-21' and InventoryFrozenDispDays<'-15', '3 weeks',

  if(InventoryFrozenDispDays>='-29' and InventoryFrozenDispDays<'-22', '4 weeks',

    if(InventoryFrozenDispDays>='-37' and InventoryFrozenDispDays<'-30', '5 weeks',

          if(InventoryFrozenDispDays>='-45' and InventoryFrozenDispDays<'-28', '6 weeks',

            if(InventoryFrozenDispDays<'-48', '+7 weeks'))))),'3 weeks','4 weeks','5 weeks','6 weeks','+7 weeks')

Unfortunately it isn't working... The order displayed is  '3 weeks','4 weeks','6 weeks','+7 weeks','5 weeks'. Do you have any idea what I might be doing wrong?

Thank you,

Sofia Vaz

[sunny_talwar · Accepted solution]

Try using dual for your calculated dimension and sort numericallu

=if(InventoryFrozenDispDays>='-21' and InventoryFrozenDispDays<'-15', Dual('3 weeks', 1),

  if(InventoryFrozenDispDays>='-29' and InventoryFrozenDispDays<'-22', Dual('4 weeks', 2),

    if(InventoryFrozenDispDays>='-37' and InventoryFrozenDispDays<'-30', Dual('5 weeks', 3),

          if(InventoryFrozenDispDays>='-45' and InventoryFrozenDispDays<'-28', Dual('6 weeks', 4),

            if(InventoryFrozenDispDays<'-48', Dual('+7 weeks', 5))))))

[Anonymous]

Thank you Sunny, this solution is great!