Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

wdchristensen
Valued Contributor

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

Tags (1)
7 Replies
wdchristensen
Valued Contributor

Re: Sorting By Expression (Months of Fiscal Year)

AncientAliens.jpg

Re: Sorting By Expression (Months of Fiscal Year)

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

Re: Sorting By Expression (Months of Fiscal Year)

can you share your app??

wdchristensen
Valued Contributor

Re: Sorting By Expression (Months of Fiscal Year)

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

Re: Sorting By Expression (Months of Fiscal Year)

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

Re: Sorting By Expression (Months of Fiscal Year)

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)

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.

Community Browser