Qlik Community

Healthcare User Group

Announcements
Submit your remarkable customer story for the Global Transformation Awards at QlikWorld Denver 2022. SUBMIT A STORY
cancel
Showing results for 
Search instead for 
Did you mean: 
wdchristensen
Specialist
Specialist

Sort By Expression – Qlik Sense

I have a situation where I want to reorder a dimension based off a custom expression. In this example, I am using a Fiscal Year that starts in September. I don’t want to use a master calendar or a dual. I really want to understand why my current solution is not working and how to modify the formula so it will work as expected. SortByExpressionFiscalYear.png

7 Replies
wdchristensen
Specialist
Specialist
Author

Only({1}Mod((Month(AdmitDate1)+3), 12))

wdchristensen
Specialist
Specialist
Author

I was able to get it working based on another example but I don't really understand why set analysis is needed in this situation. An explanation would be greatly appreciated!

burak_ersoy
Contributor III
Contributor III

Hello wdchristensen‌,

I am not sure about the set analysis script above. However, I have seen people added a MonthNbr field to Master Calendar and number the months as 1 for Jan, 2 for Feb, etc... Then you can use the =MonthNbr field in the sort by expression script. This is an alternative solution that requires one time data architecture change, but you can use it with all your apps. In addition, it should be a lot more efficient than applying set analysis for very time chart will be generated.

Hope it helps.

Thanks,

Burak

wdchristensen
Specialist
Specialist
Author

Hi Burak,

I appreciate the feedback. From a performance perspective I am pretty sure a Master Calendar solution will perform faster in the app since the function won't have to do the calculation at run time. However, I am still unsure why a set expression is needed in the sort expression. If this solution was implemented in SQL, the aggregate sql query would not require you to aggregate a field just to use it in the sort order.

In Qlik many know HOW and FEW know WHY 

TrinityTheMatrix.PNG

SteveNewman
Employee
Employee

‌Why not something like...

=num(month([Date1]))+If(month([Date1])<9,12,0)

This way you just shift the number if the month is sept, oct, nov, dec and leave the rest alone. Since it is only used in your sort expresssion this should have no impact on your data results

wdchristensen
Specialist
Specialist
Author

SortByExpressionFiscalYear01.png

Hi Steve,

Thanks for the suggestion. I tried your solution and although the logic seems like it should work, it produces the same strange behavior. Additionally, it can be put in a set analysis expression like the one below and then it works as expected.

Only({1} num(month([Date1]))+If(month([Date1])<9,12,0)) /* only works in set analysis */

I have no issues with making it work, my issue is with understanding solutions like the one you posted originally don't work. 

burak_ersoy
Contributor III
Contributor III

Dear wdchristensen‌,

I guess the answer for your question is hidden in QIX Engine mysteries. Therefore, I am not sure if this is the best user group (community) to ask this question. I believe Scripting community will be a better place to find the WHYs of QIX Engine.

Thanks,

Burak