Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wdchristensen
Specialist
Specialist

Sorting By Expression (Months of Fiscal Year)

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.

StrangeSortBehavior.png

Qlik Sense 3.2

Labels (1)
8 Replies
wdchristensen
Specialist
Specialist
Author

AncientAliens.jpg

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
Creator III
Creator III

can you share your app??

wdchristensen
Specialist
Specialist
Author

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
Specialist
Specialist
Author

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)

Capture.PNG

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
Contributor II
Contributor II

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