topic Re: Sorting in cyclical group - unsolved in QlikView

Sorting in cyclical group - unsolved

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I am struggling to make the cyclic group sorting work but not finding any luck.

I have 3 dimensions in a group. MonthName, Specialty and Consultant.

I went into Chart Properties>Sort, unticked everything and choose 'Override Group Sort Order'

I went into Document Settings > Document Properties > Groups > Edit, promoted the MonthName to first in the order.

I went into Sort Orders, chose MonthName and said Match(MonthName, 'Apr-15', 'May-15',etc.)

For Specialty, I chose Expression descending and copied the expression that I used in the chart (This is because I can't see a Y-value descending here).

For consultant, I followed the same above.

The outcome I am getting for Months is it starts with Apr-16 (instead of Apr-15) and goes up to Nov-16, and goes to Jan-16 (skipping Dec-15) and then Mar-16 (skipping Feb-16). The data does exists for those months and it is jumbled, so not sure why.

Any help is appreciated.

Thanks,

karthik

Re: Sorting in cyclical group - unsolved

Colin-Albert — Tue, 13 Dec 2016 12:16:42 GMT

If you created the Monthname field using the monthname() function, the field should be sorted numerically as it is a dual field.

Your expression is sorting the field as text hence April is shown first.

What is wrong when the data is sorted using the Default Group Sort Order?

Have a look at this explanation of Dual() How to use- Dual()

Re: Sorting in cyclical group - unsolved

thomaslg_wq — Tue, 13 Dec 2016 12:20:19 GMT

Hi Karthik,

Could you post an example.qvw with scrambled data to help you ?

You may want to create your [MonthName] field a dual value (text and numeric) and then sort by numeric value.

regards,

Thomas

Re: Sorting in cyclical group - unsolved

sunny_talwar — Tue, 13 Dec 2016 12:20:46 GMT

I think you need to uncheck 'Override Group Sort Order' because you want your chart to be sorted by whatever you have set within your cycle group for sort order. Right now you are telling your chart to over-ride what is available for sorting within the cycle group properties and use one of the options available within the chart

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 12:47:37 GMT

Hi Colin,

Thanks for the reply. I am using the below for month name

Date(MakeDate(Year(Attendance_Date), Month(Attendance_Date)) ,'MMM-YY') as MonthName

I wanted the other dimensions to be sorted by Y-value descending (expression descending).

When I tried the Default option as per your suggesion, I am getting other dimensions sorted by y-value descending but I am getting the monthname starting from Nov-16.

Re: Sorting in cyclical group - unsolved

thomaslg_wq — Tue, 13 Dec 2016 12:50:29 GMT

Just add the month function so :

month(MakeDate(Year(Attendance_Date), Month(Attendance_Date))) as MonthName

Re: Sorting in cyclical group - unsolved

Colin-Albert — Tue, 13 Dec 2016 12:52:06 GMT

Use the monthname function and the data will be created as a dual field and will sort correctly.

monthname(Attendance_Date) as MonthName

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 12:53:15 GMT

Hi all,

I have attached the sample file. Please let me know.

Thanks,

Karthik

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 12:57:11 GMT

Thanks Thomas, I tried this but still didn't work. It sorts from Jan Oct Nov Sep, etc..

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 12:57:45 GMT

Tried this Colin, but it gives Jan 16, Sept 15, Nov 2015, etc..

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 13:00:35 GMT

Thanks for the reply Sunny. When I try the 'Override Group Sort Order', it sorts the months correctly,but it is not allowing the other dimensions to sort by y-value (expression) descending.

Re: Sorting in cyclical group - unsolved

thomaslg_wq — Tue, 13 Dec 2016 13:06:26 GMT

You may need edit the group => sort order :

Month : numeric value ascending

Others : expressions ==> put the same measure as the one in the chart.

And in the chart, un select every check box in the sort otab.

This workaround should work

Re: Sorting in cyclical group - unsolved

infock12 — Tue, 13 Dec 2016 13:52:21 GMT

That worked perfectly Thomas!! Thank you very much.