topic Re: Sort in Straight Table a Expression by a equation in QlikView

Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 16:56:24 GMT

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,

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 17:05:43 GMT

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

Re: Sort in Straight Table a Expression by a equation

maxgro — Wed, 19 Feb 2014 17:07:11 GMT

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)

Re: Sort in Straight Table a Expression by a equation

Nicole-Smith — Wed, 19 Feb 2014 17:49:00 GMT

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:

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 19:19:57 GMT

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

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 19:23:27 GMT

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

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 19:25:25 GMT

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

Re: Sort in Straight Table a Expression by a equation

Nicole-Smith — Wed, 19 Feb 2014 19:26:01 GMT

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

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 19:35:21 GMT

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

Re: Sort in Straight Table a Expression by a equation

Clever_Anjos — Wed, 19 Feb 2014 19:37:15 GMT

Use this expression as sorting order

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

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 19:50:11 GMT

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

Re: Sort in Straight Table a Expression by a equation

Nicole-Smith — Wed, 19 Feb 2014 19:53:40 GMT

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

Re: Sort in Straight Table a Expression by a equation

danielact — Wed, 19 Feb 2014 19:55:36 GMT

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.

Re: Sort in Straight Table a Expression by a equation

Wed, 19 Feb 2014 20:46:32 GMT

Nicole, excelent, its great, thank you

Re: Sort in Straight Table a Expression by a equation

Nicole-Smith — Wed, 19 Feb 2014 20:50:13 GMT

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