topic List Box sorting based on Expression in QlikView

List Box sorting based on Expression

azimabadi — Tue, 29 Jan 2013 10:14:41 GMT

Hi everybody,

i have a list box containing the Months. I need to sort this list box so that list begins with "Jul". I did this with a sort expression :

= MOD( ( Calendar.Month + 5 ) , 12 ) + 1

Works good! But as soon as i select one or more months, i get this result :

I understand because of my selection, the "Calendar.Month" is NULL for all nonselected Months and therefor QV can not calculate the expression.

But i need the result as is shown below:

It's like the way QV shows the List Box when i select "Numeric Value" as Sort order:

that means i want to keep the order from my Expression as i select some Months.

I appreciate any idea.

Best regards - Peyman

Re: List Box sorting based on Expression

Tue, 29 Jan 2013 10:33:00 GMT

You have to tell QlikView that the expression should be calculated on the full model instead on only on the selection. This is done with the set analysis expression {1}. I think it should be something like:

= MOD( ( Only({1} Calendar.Month) + 5 ) , 12 ) + 1

Re: List Box sorting based on Expression

alexandros17 — Tue, 29 Jan 2013 10:38:31 GMT

Here is an example, hope it helps

Re: List Box sorting based on Expression

Tue, 29 Jan 2013 10:42:25 GMT

Hi Peyman,

You can calculate the position of the month in the script.

Then in the List Box, your expression will be Sum({1} DISTINCT Calendar.MonthOrder) and then, even if you select a month, the order will be show for all months.

Hope it helps.

Best regards,

Nicolas

Re: List Box sorting based on Expression

Tue, 29 Jan 2013 10:47:07 GMT

Based on the alexandros17 sample, I have adapted the expressions with the MOD( ( Only({1} order) + 5 ) , 12 ) + 1 formula:

Re: List Box sorting based on Expression

azimabadi — Tue, 29 Jan 2013 11:04:53 GMT

Hi Nicolas,

in this case i need to change begining Month dynamicly on interface, user can select the begining month and i have to calculate some expressions based on this selected begining Month. That is why i can't calculate a new order in my script.

thank you so much

Peyman

Re: List Box sorting based on Expression

azimabadi — Tue, 29 Jan 2013 11:06:31 GMT

Thx so much JG,

i'm surpreised how i forget everytime set analysis!!!

Best regards

Peyman