Sorting by Filter pane Asc or Desc

[Flxvio043]

Hi all,

I have a question regarding my Qliksense. I would like to create a filter pane with the selection Acending and Decending and then filter on the selection of the user a Gantt chart with Countrys Acending or Decending. So for example if ASC is selected it starts with Australia and if Desc is selected it starts with zimbabwe. I already tried to change sort by expression in qliksense gantt chart and inserted this code without success:
if(GetFieldSelections(Sorting)='Acending', Asc, Desc).
In summary, if Acending is selected in the Sorting field, it should sort countries by asc and if decending is selected by cesc.

Does anyone have a solution for this or can provide me with the code.

Thank you very much and best regards

Flavio

[stevedark · Accepted solution]

HI @Flxvio043 

I don't know about the Gannt chart extension you are using, I but I presume the sorting is the same as other Qlik objects.

With the sort by expression the result always needs to be numeric in order to sort on it. It also has to be the value you wish to sort, rather than just Asc or Desc.

So, to sort on the first character you will need to do:

=ord(left(Country,1)) * if(GetFieldSelections(Sorting)='Descending', -1, 1)

Note the change in the IF to make Ascending the default if nothing is selected.

Obviously, there are some first letters with more than one country, so you will need to build up the sort number:

=((ord(left(Country,1))*100000)+(ord(mid(Country,2,1))*1000)+(ord(mid(Country,3,1))*10)+(ord(mid(Country,4,1)))) * if(GetFieldSelections(Sorting)='Descending', -1, 1)

That still only sorts the first four characters, and case of characters will make a difference (you could use lower to fix this).

It's probably better to create a CountrySort field in the load script and then use that. You can do that by adding this the end of the script:

CountrySort:
LOAD
   maxstring(Country) as Country,
   RowNo() as CountrySort
RESIDENT YourDataTable
GROUP BY Country
ORDER BY Country;

The maxstring is a little confusing there, as it is doing nothing, but it is required you have at least one aggregation function when you use GROUP BY.

In the sort order for the gannt you can then just have:

=CountrySort * if(GetFieldSelections(Sorting)='Descending', -1, 1)

Personally, I would probably look at buttons and variables for the toggle between ascending and descending, but the filter pane will work, if you want to go that way.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[Flxvio043]

Hi @stevedark 

Thank you so much for your help. I choosed the second option with CountrySort in the load script and it works perfect. I would have a second question and I don't know if this is fisible or not. In this Gantt Chart it shows Starting and End Dates (Actual Start Date as a Field and Actual End Date as a Field). I am wondering if it is possible to create a custom sort filter that not only allows for ascending and descending sorting but also includes a third option to filter by the earliest start date to the latest start date. Do you think this is feasible? 

The Filter Pane would look like this:

I tried it but i did not get it. Do you maybe have a idea. I think it could work pretty simular to the top

Again many thanks and best regards

Flavio

[stevedark · Accepted solution]

Hi @Flxvio043 

Glad that worked out for you.

That's exactly what the sort by expression is there for. The expression would be:

=if(GetFieldSelections(Sorting)='First Approval', min([Actual Start Date]),
CountrySort * if(GetFieldSelections(Sorting)='Descending', -1, 1))

You could add other sort orders as you wish and just nest them in the IF statement.

You could also have two filters, one for sort type (Country, First Approval, Completion etc.) and a separate one for Ascending and Descending. This would give you lots of flexibility.

As dates are just numbers multiplying them by -1 will work in the same was as the Country sort.

Hope that helps.

Steve

[Prem0212]

Drag the filter pane option to the sheet and in the properties section u will find the sorting option there u can choose the options as like

[Flxvio043]

Hi @stevedark 

Thank you so much. You are a genius🙂. That this works now made my day.

I would have one last question. Is there a Option if there is no Date entered yet to have them at the end and not at the beginning:

Because the min function looks for the minimum and of course nothing is less than if there is a date but it would be better if they are at the end.

Thank you. You really made my day with that

[stevedark]

Hi @Flxvio043 

Thanks! That's how my customers feel every day. 😁

You should be able to add an alt statement to replace the null with a very high number.

=if(GetFieldSelections(Sorting)='First Approval', alt(min([Actual Start Date]), 99999999),
CountrySort * if(GetFieldSelections(Sorting)='Descending', -1, 1))

Cheers,

Steve

[Flxvio043]

Hi @stevedark ,

I hope you are doing fine.

I have noticed that when I select the Submission First filter, it filters the Actual Submission Dates in a staircase pattern, which is exactly what I wanted. However, at the end of the list, there are still some entries that do not have an Actual Submission Date but only have a Target Submission Date. I was wondering if it's possible to modify the code to include both Actual and Target Submission Dates in the filter.

This is the Code I'm using

=if(GetFieldSelections(Sorting)='Submission First', alt(min([Actual Submission Date]), 99999999)

And this is how it looks (The blue ones are planned and also should be in stare case like the orange):

Thank you very much and kind regards

Flavio

[stevedark · Accepted solution]

Hi @Flxvio043 

Instead of using the alt function, which only allows two parameters, you can use the coalesce function, which allows a whole list. It returns the first non-null value.

So, your expression would be:

=if(GetFieldSelections(Sorting)='Submission First', coalesce(min([Actual Submission Date]), min([Planned Submission Date]), 99999999)

It looks like the 99999999 may not be needed, if all values have a planned date, but it is there as a backstop.

Hope that works for you,

Steve

[Flxvio043]

Hi @stevedark ,

You are the Best! Thank you very much it works fine.

Best regards