topic Sort Expression in straight table... in QlikView

Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 05:36:19 GMT

Hello Community,

i have a problem with sorting in straight table.. let me explain me..

i have a pivot table with two dimension and 3 expressions.

my expression are below:

1) SUM({<Brand = {"$(=trim(subfield(GetFieldSelections(Brand),',',1)))"}>} SellOut.Total_Sale)

2) SUM({<Brand = {"$(=trim(subfield(GetFieldSelections(Brand),',',2)))"}>} SellOut.Total_Sale)

3) column(1) -column (2)

user can select 2 Brand to see the difference , between two Brands. (i have more than 50 brands in list box)

let say if i select "SAMSUNG" AND "APPLE" in brand listbox. my chart look like below...(sample)

in the image above, store is my dimension, and APPLE is my first expression, and SAMSUNG is second Expression.

its sorted alphabetically....

but i want first column, which i select first, and second column which i select on no two..

let say if i select Samsung first, and then i select apple, then Samsung must be first column, and apple must be second...

but whatever i select it sort alphabetically all the time..

any idea....

Many Thanks

Re: Sort Expression in straight table...

Kushal_Chawda — Sun, 05 Jul 2015 05:41:59 GMT

Create straight table instead of Pivot table. In straight table you can just double click on column header to sort it.

Re: Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 06:05:55 GMT

you did not get me..?

i dont want to sort values, i want to sort the Heading...

currently i have APPLE ! SAMSUNG

But i want SAMSUNG then Apple,

is it clear?

Re: Sort Expression in straight table...

Kushal_Chawda — Sun, 05 Jul 2015 06:21:28 GMT

Sorry to misunderstood.. Can you please post the sample app?

Re: Sort Expression in straight table...

marcus_sommer — Sun, 05 Jul 2015 09:16:10 GMT

You need to put a sorting on your dimension Brand within the script, maybe by an additionally field which containde the sort-order or you used a dual-field for your dimension Brand, maybe in this way:

BrandSorting:

Load * Inline [

Brand, BrandSorting

Samsung, 1

Apple, 2

...

];

then you didn't use getfieldselection else concat to get the selected values:

SUM({<Brand = {"$(=subfield(concat(distinct Brand, ',', BrandSorting),',',1))"}>} SellOut.Total_Sale)

then concat has as third parameter a numeric sorting.

- Marcus

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 10:02:06 GMT

Hi,

if I understand right you want to order the column in the temporally order that users select the two brand from a listbox Brand (NOT a static order that you have for example in a table of the database).

Can you confirm this?

Best regards.

Re: Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 10:57:48 GMT

let me explain again...

i have two conditional expressions, that sum up data for two selected brands, user can select two brand to see the value, if nothing is selected in brand listbox then chart does not show any thing.

when user select Let say "Nokia" then first expression gets enables, and in the chart i can see two dimension and one expression column(Nokia).

when user select second brand, let say "APPLE" then second expression get enabled, and user can see the data for APPLE, and third expression is column(1)- column(2) this is difference between two selected brands..

up to here , every thing is fine..

but now, what i want ...

i selected nokia first, and then i selected apple.

so i want my first column nokia and second column apple,

but qlikview sort it alphabetically, and shows me first column Apple and second column nokia...

i just want to see at column one, which i select first..and then second column, which i select second..

in the above image, first i selected SUMSUNG, then i selected APPLE

but Samsung is at column 2, and apple become column 1.

i want Samsung at column 1, and apple at column 2..

i think its clear now...

Many thanks Guys...

Re: Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 11:02:43 GMT

Hi Marcus,

thanks for the idea..

1) i have more than 100 Brands, looking for some short way... 🙂

2) you shared the below expression.

SUM({<Brand = {"$(=subfield(concat(distinct Brand, ',', BrandSorting),',',1))"}>} SellOut.Total_Sale)

i think this expression will sum up the data for selected brands in one value, mean one column.

but i want to compare the two selected brands, and want to show the difference?

that why i have two expressions, where i sum up data for two selected brands..

Re: Sort Expression in straight table...

Kushal_Chawda — Sun, 05 Jul 2015 11:18:28 GMT

Hi,

You can do something like this.

Please see the attached

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 11:23:29 GMT

IsrarKhan,

if I ask again is only to help you better.

And I ask again because a user says to you that you kan use a priority field (STATIC order).

Excuse me but only to be clear.

So, I try do to what you need with alternate state.

Try if this can is good for you.

Regards.

Re: Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 11:32:23 GMT

this is what i have currently...

when you select samsung and apple in your brand list, then all the time apple comes as first column..even if you select sumsung first...

i want first column which i select first...

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 11:37:34 GMT

I'm very happy this can help you.

I try also to find a way to save in which order user select the values in a listbox but I dont find nothing, unfortunatelly.

Best regards.

Re: Sort Expression in straight table...

israrkhan — Sun, 05 Jul 2015 11:37:52 GMT

yes, this is what i want...

but without alternate state.... 🙂

i have more than 100 brands, in your suggestions, i have to put two list boxes for same brands,

and user will chose two brands from two list boxes...

thanks for your time...really appreciate your effort..

many thanks

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 11:50:19 GMT

You are wellcome!

I'm very interesting if it is possible to store the users selection order in a Listbox.

Best regards.

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 11:57:04 GMT

surely we can help once u post ur application here...

Re: Sort Expression in straight table...

Anonymous — Sun, 05 Jul 2015 12:17:35 GMT

This can be done by changes in the script. there you have to mention the way u want it..done the same thing in the past...

Re: Sort Expression in straight table...

marcus_sommer — Sun, 05 Jul 2015 12:33:27 GMT

No, it's not sum all all possible brand-values - it takes only the first brand-value and it meant you need further to use two expressions - I have only one expression showed as example, therefore:

SUM({<Brand = {"$(=subfield(concat(distinct Brand, ',', BrandSorting),',',1))"}>} SellOut.Total_Sale)

SUM({<Brand = {"$(=subfield(concat(distinct Brand, ',', BrandSorting),',',2))"}>} SellOut.Total_Sale)

column(1) -column (2)

It's only the same like your example only that it used concat which offered as adavantage against getfieldselection a sorting option and which could be extend with additional condition maybe with an included set analysis.

And I fear it's the easiest and shortest solution. You could of course try to define the order with variables and actions but this is quite complex and it will fail if there is a multiple selection at one time (for example selection by pressing the ctrl-key). Further you could check which Brand has the most or max. value or similar and ordered them to these value.

Another very easy possibilty is to use two fields and listboxes (same values but different fieldnames) with the option to allow only one selected value and use listbox one for expression one and listbox two for expression two.

- Marcus