Sort in Straight Table a Expression by a equation

[#1]

hi,

First, Sorry by my poor english,

I have a Straight Table with one dimension, and many expresion, a expresion is for example:

=IF(SUM({<Year_Sales={2013}}>} distinct Sales)/1000000000>=1,

'$ ' & num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000000,'####,###.#') & ' B',

IF(SUM({<Year_Sales={2013}}>} distinct Sales)/1000000>=1,

'$ ' & num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000,'####,###.#') & ' MM',

IF(SUM({<Year_Sales={2013}}>} distinct Sales)/1000>=1,

'$ ' & num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000,'####,###.#') & ' k',

num((SUM({<Year_Sales={2013}>} distinct Sales)),'####,###.#'))))

then, when I try sort this expresion, how is a text, QlikView sorting so

Sales

$ 1.7 MM

$ 1.8 MM

$ 12.6 MM

$ 145.3 K

$ 172.9 K

but, I need what is sort by numeric value, Someone can help me? thanks,

I try especified a expresion but this item is lock,

[#2]

Thanks Clever, but I need sort by expresion, not for dimension, and in the expresion this box are disable.

and  Sales is not only expresion in the chart, so the user can't sorting by other expresion with similar characteristics

[Nicole-Smith]

One more try:

IF(SUM({<Year_Sales={2013}>} distinct Sales)/1000000000>=1,

dual(num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000000,'$ ####,###.# B'),SUM({<Year_Sales={2013}>} distinct Sales)),

IF(SUM({<Year_Sales={2013}>} distinct Sales)/1000000>=1,

dual(num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000,'$ ####,###.# MM'),SUM({<Year_Sales={2013}>} distinct Sales)),

IF(SUM({<Year_Sales={2013}>} distinct Sales)/1000>=1,

dual(num((SUM({<Year_Sales={2013}>} distinct Sales))/1000000,'$ ####,###.# k'),SUM({<Year_Sales={2013}>} distinct Sales)),

num((SUM({<Year_Sales={2013}>} distinct Sales)),'$ ####,###.#'))))

[danielact]

Yes, but if you use that expression as the sort for the dimension, it will sort it based on the expression. Use the formula given above - SUM({<Year_Sales={2013}}>} distinct Sales) as the expression for sorting the Dimension. Also, make sure you promote the Dimension to the top of the sorting list.

[#5]

Nicole, excelent, its great, thank you

[Nicole-Smith · Accepted solution]

Please mark helpful and correct answers so others can find solutions to their problems too