topic Re: Month and Quarter sorting in excel in QlikView

Month and Quarter sorting in excel

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

Hi,

I have a straight table in which Quarter and Month are dimensions.

Month (Jan, Feb... Dec), Quarter (Jan-Mar 2016, Apr-Jun-2016...... Oct-Dec2017).

These 2 fields are properly sorting in straight table, however once i export into excel if i change sorting in excel (It goes only to A-Z or Z-A), is there anyway i can sort in excel same like Qlik straight table (like dual functionality)?

Thanks,

Raju

Re: Month and Quarter sorting in excel

Anil_Babu_Samineni — Tue, 12 Dec 2017 16:28:08 GMT

Does Ctrl + Shft + L and then use "Sort smallest to largest" / "Sort largest to smallest"

Re: Month and Quarter sorting in excel

raju_salmon — Tue, 12 Dec 2017 16:40:03 GMT

Thanks Anil for Reply, still it sort Apr, Aug, Dec...Sep (A-Z), but i need to sort like Jan,Feb...Dec.

Re: Month and Quarter sorting in excel

Anil_Babu_Samineni — Tue, 12 Dec 2017 16:43:34 GMT

Not sure, How you are going to exporting? Can you give your process done

Re: Month and Quarter sorting in excel

raju_salmon — Tue, 12 Dec 2017 16:53:58 GMT

Right click on the table box/Straight table and click on send to export. Once exported to excel, it does have Jan-Dec sorting only. And i did sort in excel A-Z then it goes Apr,Aug... Sep.... i want to sort it like Jan...Dec.

Re: Month and Quarter sorting in excel

Anil_Babu_Samineni — Tue, 12 Dec 2017 17:01:50 GMT

Succeed !!! But, Not sure why you needed A-Z again. It's already been correct placed. Because, When ever we are exporting direct from View report it has to be same report where i believe. And test done, And it works as expected? Image please or better if you provide something to investigate

Re: Month and Quarter sorting in excel

raju_salmon — Tue, 12 Dec 2017 17:05:37 GMT

I think i have not explained properly it seams. User can sort on any KPI (assume sales), then month order changes. Then if he wants sort based on month order, it is not possible in Excel. Then he has to sort in QV dashboard table then he has to export to excel.

Here the question was, is there any chance where user can sort month wise once he sort on any KPI which changes month order.

Re: Month and Quarter sorting in excel

alis2063 — Tue, 12 Dec 2017 17:06:12 GMT

In the Sorting tab,

You can write the Match function which will be return True values as 1,

Sort by Expression

If(match(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))

Regards,

Ali

Re: Month and Quarter sorting in excel

raju_salmon — Tue, 12 Dec 2017 17:15:45 GMT

Thanks Ali, but it does not support in excel. Once we export to excel, it is out of Qlik capability. So the question is how excel support to sort in back?

Re: Month and Quarter sorting in excel

Anil_Babu_Samineni — Tue, 12 Dec 2017 17:21:33 GMT

Does this question related Qlikview / Excel my dear friend ??

If it is Qlikview related i believe we will get as expected

If Excel, I think we need some more information - You need to understand how Excel works and how sort offers much better where i explained little earlier.

Re: Month and Quarter sorting in excel

its_anandrjs — Tue, 12 Dec 2017 18:18:00 GMT

What is actual requirement.

Re: Month and Quarter sorting in excel

trdandamudi — Tue, 12 Dec 2017 18:34:34 GMT

It is a excel solution that you want but you posted on Qlikview community ? Any have below is the screen shot that you need to follow to sort in Excel the way you want. once you press ok it sorts exactly the way you want.Hope this helps:

Re: Month and Quarter sorting in excel

johnca — Tue, 12 Dec 2017 18:34:40 GMT

I don't of a possible way for the Quarters, but for the months...maybe. If one of the fields for the Month was available as 1/1/2017, 2/1/2017, 3/1/2017, etc. it can be formatted in Excel: Customer Format = mmm. It will then show the month as Jan, Feb, Mar, etc.. Alternately, Customer Format = mmm/yyyy would show Jan/2017, Feb/2017, Mar/2017, etc. Of course, it would have to be formatted after the fact...I mean, after the export.

HTH,

John

Re: Month and Quarter sorting in excel

raju_salmon — Wed, 13 Dec 2017 04:01:12 GMT

Thanks John, most helpful.