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]

Hi, you could try to mark sort by expression, then paste the expression into the box, and then deselect numeric value and text options. i believe it should do the trick.

Kind regards,

Lukasz

[maxgro]

can you sort by expression if you select Dimension (Dimension is a dimension in your chart?)

if yes check expression and try with

SUM({<Year_Sales={2013}>} distinct Sales)

[Nicole-Smith]

Try changing your expression to:

=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)),'####,###.#'))))

Or enter your symbols on the Number tab in the symbol boxes:

[#5]

Thanks Nicole, but this boxes only are available on graph chart, in Strigth Table are disable,

if I change the expresion, is possible sort how number but QlikView show that 906.2K is bigger than 6.3MM, or that 525.6K is bigger than 425.0MM, and this is a mistake,

Sales

$ 906.2K

$ 894.8K

$ 546.9K

$ 525.6K

$ 425.0MM

$ 170.6K

$ 158.2K

$ 151.3K

$ 145.3K

$ 122.6K

$ 77.6K

$ 6.3MM

[#6]

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

[#7]

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

[Nicole-Smith]

At first I missed that you were adding on a dollar sign.  The following should work:

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)),'$ ####,###.#'))))

[#9]

Thanks Nicole, but QlikView sort how number, so show that $ 1.6 Billon < $ 15 Millon, and this is a mistake,

[Clever_Anjos]

Use this expression as sorting order

SUM({<Year_Sales={2013}>} distinct Sales)