Sorting a bar chart using an expression

[ToddE]

Hi Qlik Community

I have a bar chart which uses a dimension where the values begin with letters and numbers.  Regardless of the value of the measure, I want the bars to be ordered as follows:

• Expired
• 0-90 Days
• 91-180 Days
• 181-365 Days
• 365+ Days
• No Expiry

Can I achieve this using an expression?

Thank you

[Oleg_Troyansky]

Hi Todd,

Yes, this is possible. The best way of solving this problem is to assign dual values to each of these labels. For example:

'Expired' should be replaced with Dual('Expired', -1)

'0-90 Days' should be replaced with Dual('0-90 Days', 0)

'91-180 Days' should be replaced with Dual('91-180 Days', 91)

etc...

This way, you should be able to display text and to sort by the corresponding number.

Cheers,

[ToddE]

Hi Oleg

Thank you for taking the time to reply.  Unfortunately I am a novice and although I have tried using dual, I can't get it to work for me.

The expression for my dimension is as follows:

IF(ISNULL([Days to Expiry]), 'No Expiry Date',
IF([Days to Expiry]=0, 'Expired',
IF([Days to Expiry]<=90, '0-90 Days',
IF([Days to Expiry]<=180, '91-180 Days',
IF([Days to Expiry]<=365, '181-365 Days', '365+ Days')))))

You can see it's very basic and I have no idea where to include dual.  I have tried adding to the expression for the dimension and I have tried using in 'Sort by expression'.  Neither work, but it's definitely because I am doing something wrong.

[marcus_sommer · Accepted solution]

You may apply it in this way:

IF(ISNULL([Days to Expiry]), dual('No Expiry Date', 0),
IF([Days to Expiry]=0, dual('Expired', 1),
IF([Days to Expiry]<=90, dual('0-90 Days', 2),
IF([Days to Expiry]<=180, dual('91-180 Days', 3),
IF([Days to Expiry]<=365, dual('181-365 Days', 4), dual('365+ Days', 5))))))

[ToddE]

Thank you Marcus, I appreciate your support.  I have removed what I had previously and replaced with your suggested expression, but I am receiving an 'error in expression' message. 

[marcus_sommer]

Should not be - just checked the syntax on my side and there is everything ok. Therefore a direct copy & paste should be working or you take a closer look on the brackets, quotes and commas which are probably misplaced.

[Oleg_Troyansky]

Try this:

IF(ISNULL([Days to Expiry]), DUAL( 'No Expiry Date', -1),
IF([Days to Expiry]=0, DUAL( 'Expired', 0),
IF([Days to Expiry]<=90, DUAL( '0-90 Days', 90),
IF([Days to Expiry]<=180, DUAL( '91-180 Days',, 180),
IF([Days to Expiry]<=365, DUAL('181-365 Days', 365) , DUAL( '365+ Days', 366)

)))))

And sort the dimension in the NUMERIC order, ASCENDING.

Cheers,

[ToddE]

Hi Marcus...you were right...user error! Thank you 🙂