topic Re: Sorting By Expression (Months of Fiscal Year) in App Development

Sorting By Expression (Months of Fiscal Year)

wdchristensen — Sat, 16 Nov 2024 08:43:18 GMT

I was attempting to reorder the months based on the start of the fiscal year. My first attempt at this was adding “Mod((Month(Tbl01_Date)+3), 12)” to a custom sort expression as shown below in Line Chart 2 below. I would like to know why this didn’t sort as expected. I eventually was able to achieve the desired sort order with the expression “Only({1}Mod((Month(Tbl01_Date)+3), 12))” as shown in Line Chart 1. I would like to know why I had to use a set analysis expression to make this work. I would appreciate any help you can provide in understanding this strange behavior.

Qlik Sense 3.2

Re: Sorting By Expression (Months of Fiscal Year)

wdchristensen — Mon, 12 Feb 2018 14:51:12 GMT

Re: Sorting By Expression (Months of Fiscal Year)

sunny_talwar — Mon, 12 Feb 2018 14:54:49 GMT

Were you making selections when you tried just this

Mod((Month(Tbl01_Date)+3), 12)?

If you did, then the expression is only populated where it is in the selection, whereas you might be using set analysis to show more than your default selection. So, in order for the sort expression to sort the unselected stuff, it either needs the same set analysis like your expression or use {1} to ignore everything

Re: Sorting By Expression (Months of Fiscal Year)

big_dreams — Mon, 12 Feb 2018 14:57:48 GMT

can you share your app??

Re: Sorting By Expression (Months of Fiscal Year)

wdchristensen — Mon, 12 Feb 2018 17:03:45 GMT

Please see the attached application illustrating the issue occurring in Qlik Sense 3.2.

Re: Sorting By Expression (Months of Fiscal Year)

sunny_talwar — Mon, 12 Feb 2018 17:24:07 GMT

It appears that you have multiple years which might have been causing this... Using Max() function seems to resolve the issue

Mod((Month(Max(Tbl01_Date))+3), 12)

Re: Sorting By Expression (Months of Fiscal Year)

wdchristensen — Mon, 12 Feb 2018 17:35:50 GMT

Hi Sunny, I appreciate your reply. As you can see from the application I already have a working solution which utilizes an aggregate function (Only). I know from trial and error that the solution is to use an aggregate function to resolve the issue, what I don’t know is "why". If for example the year was the issue, what exactly about the year would be the issue? Additionally, you would see the data sort correctly if only one year of data was brought in. Any other thoughts?

Re: Sorting By Expression (Months of Fiscal Year)

sunny_talwar — Mon, 12 Feb 2018 19:21:07 GMT

I am not entirely sure why this happens (could be a bug), but if I create Month(Tbl01_Date) as dimension and the expressions from the image, it seems that all of them work (without selection) except Mod((Month(Tbl01_Date)+3), 12)

Now, I have always thought that Having a Only (without set analysis) is equivalent of not having any only, but this seems to defy that. Could be a Qlik Sense bug and may be mto‌ might be able to confirm or reject the findings.

Re: Sorting By Expression (Months of Fiscal Year)

Akshaya45 — Thu, 18 Apr 2019 11:30:15 GMT

Hi Wdchristensen,

your Expression is giving from Sep onward for any selection from filter Pane,

If i want based on selection means , if i want to select from june than graph will be show from June to May continuously.

Please help how do i achieve above scenario.

Regards,

Akshaya