Sorting By Expression (Months of Fiscal Year)

[wdchristensen]

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

[wdchristensen]

[sunny_talwar]

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

[big_dreams]

can you share your app??

[wdchristensen]

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

[sunny_talwar]

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)

[wdchristensen]

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?

[sunny_talwar]

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.

[Akshaya45]

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