topic Re: Problem sorting chart with conditional expressions in QlikView

Problem sorting chart with conditional expressions

patroser — Sat, 20 Jun 2015 11:20:58 GMT

Hi there,

I've an issue sorting my chart.

The use case is, that I have a chart calculating values for the last 12/24 rolling months or 4/8 rolling quarters. Therefore my chart shows wheter 4, 8, 12 or 24 columns, depending on the selection of type (quarterly/monthly) and duration (12/24 months).
To sum those values up I've got one more expression calculating the total of the last 12/24 rolling months or 4/8 rolling quarters.

I'd like to always sort my chart by this total column, but as soon as I change one of the options type or duration the chart loses the sort setting of sorting by this Total column descending.

Attached you can find a sample application with the same problem.

I'd like to sort by the total column, therefore I change the priority of the total column in the chart to the top and select sort by numeric value descending.

As soon as i switch the type, the sort order is set to default again.

Any suggestions how I could avoid this problem?
I don't want to change the straight table to a pivot chart.

Thanks in advance

Patrick

Re: Problem sorting chart with conditional expressions

sunny_talwar — Sat, 20 Jun 2015 11:57:05 GMT

Try this:

Best,

Sunny

Re: Problem sorting chart with conditional expressions

patroser — Sat, 20 Jun 2015 12:27:35 GMT

I already took this Option into account.

Unfortunately this brings up another problem. I've got more than just one dimension.

Sorting dim1 by expression leads to the total column not being sorted completely descending. I'm writing from my smart phone, I hope I can explain the example properly:

Dimension1	Dimension2	Total
Customer 5	variant 02	534
Customer 5	variant 01	236
Customer 23	variant 02	610
Customer 23	variant 01	34
Customer 23	variant 03	27
Customer 13	variant 03	465
Customer 13	variant 01	68

But the sort order I would like to have is:

610

534

465

236

Thanks

Patrick

Re: Problem sorting chart with conditional expressions

sunny_talwar — Sat, 20 Jun 2015 14:23:37 GMT

I see what you are saying now. While looking for a permanent solution still, I can suggest a work around of placing two charts on top of each other and conditionally hide and show them based on your GetCurrentField() selection.

Re: Problem sorting chart with conditional expressions

sunny_talwar — Sat, 20 Jun 2015 14:30:28 GMT

Although still a work around, but its better than having two charts. Using the following script I created a new field with a combination of Dimension1&Dimension2 and called it sort

Table:

LOAD Dimension1,

Dimension2,

Total as Value,

Dimension1&Dimension2 as Sort

FROM

[https://community.qlik.com/thread/169261]

(html, codepage is 1252, embedded labels, table is @1);

Added Sort as a dimension for you chart and hide it and use a expression sort on this new field.

Seems to work, see if this is helpful.

Best,

Sunny

Re: Problem sorting chart with conditional expressions

patroser — Sun, 21 Jun 2015 14:41:53 GMT

Thanks for your help, I think I'm going to discuss this topic with my client again.

Could be the sort by expression is still enough for him.

If it's not enough I'll need to calculate everything in the script.

Thanks!

Patrick

Re: Problem sorting chart with conditional expressions

patroser — Sun, 21 Jun 2015 14:44:37 GMT

But still I'd like to know if this behaviour is as expected or not.

Re: Problem sorting chart with conditional expressions

sunny_talwar — Sun, 21 Jun 2015 15:56:09 GMT

I am not sure if this is the intended behavior, but I have seen that this is what I have seen for some of the other charts I have made within the applications I have worked on. But since there was never an importance on getting the sorting right, I never worked on getting it right for more than 5-10 mins and moved on with what I got.

Re: Problem sorting chart with conditional expressions

sasiparupudi1 — Mon, 22 Jun 2015 08:08:27 GMT

Since you are conditionally manipulating the number of columns that are displayed in the table, the sort order is getting changed ..

I think one way to get your sorting priority constant, You need to promote your total to the top of the list in the expressions tab and set the sorting priority. This should work if you do not have a problem in displaying the total column first in the grid

Re: Problem sorting chart with conditional expressions

settu_periasamy — Mon, 22 Jun 2015 08:56:04 GMT

Hi,

Can you try the below attachment.

Re: Problem sorting chart with conditional expressions

patroser — Mon, 22 Jun 2015 09:02:42 GMT

Thanks for your advice, I think this will work for my client

Patrick

Re: Problem sorting chart with conditional expressions

patroser — Mon, 22 Jun 2015 09:05:15 GMT

Thanks!

This also looks good, but:

I'm wondering, if we conditionally hide the column instead of the expression, I guess the engine still does the math for all the columns, right?
Doesn't matter in the specific case, because there's not that much to calculate, but in cases of millions of rows, I'm afraid this solution has a significant impact on the performance.

Or am I getting it wrong?

Patrick

Re: Problem sorting chart with conditional expressions

settu_periasamy — Mon, 22 Jun 2015 09:52:25 GMT

Hi Patrick,

Actually, I tried with this (conditionally hide the column instead of the expression) application which is having millions of records. I don't find any performance issue.

May be some experts will give the proper answer for this.

Re: Problem sorting chart with conditional expressions

patroser — Tue, 25 Aug 2015 14:51:56 GMT

Just 4 Info:

I think I figured out, that just hiding the column instead of the expression is indeed of better performance.

We had close to 40 conditional expressions.

Working with the qvw itself wasn't a problem, but working via "open in server" took about one minute to load the chart containing the conditional expressions.

Switching the condition from expression to column improved the performance. Afterwards it just took a few seconds to load the chart.