Sort By Expression – Qlik Sense

[wdchristensen]

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.

[wdchristensen]

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

[wdchristensen]

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]

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]

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 

[SteveNewman]

‌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]

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]

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