Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Please mark helpful and correct answers so others can find solutions to their problems too
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
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)
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:
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 |
Thanks Lukasz, but I need sort by expresion, not for dimension, and in the expresion this box are disable.
Thanks Massimo, but Sales is not only expresion in the chart, so the user can't sorting by other expresion with similar characteristics
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)),'$ ####,###.#'))))
Thanks Nicole, but QlikView sort how number, so show that $ 1.6 Billon < $ 15 Millon, and this is a mistake,
Use this expression as sorting order
SUM({<Year_Sales={2013}>} distinct Sales)