How to apply sorting on dates in the script?

[Vinil]

Temp_Calendar:

LOAD
$(vStartDate)+ (RowNo() - 1) AS DateNum
AutoGenerate 1 While $(vStartDate) + (RowNo() - 1) < $(vEndDate)-1;

NoConcatenate

//To generate all date combinations for Master Calendar
[$(vQDOS_Table)]:

LOAD Rolling30Days,
FileDate,
Date,
YearNumber & HalfYearly AS YearHalfKey,
YearNumber & Quarter AS YearQuarterKey,
YearNumber &'M'& MonthNum AS YearMonthKey,
YearNumber AS YearKey
;

LOAD *,

Year & Num(Month(DateNum), '00') & Num(Day(DateNum), '00') AS [FileDate],
IF(Quarter ='Q1' OR Quarter ='Q2', 'H1',
IF(Quarter ='Q3' OR Quarter ='Q4', 'H2'))
AS HalfYearly,

ApplyMap('Map_Year_To_Number', Year, '<Unknown>')
AS YearNumber,

if(DateNum >$(vRolling30Days) and DateNum<=Today(),1,0)
AS Rolling30Days
;

LOAD
DateNum,
Date(DateNum) AS Date,
num(Month(DateNum)) AS MonthNum,
'Q' & Ceil(Month(DateNum) / 3) AS Quarter,
Year(DateNum) AS Year
Resident Temp_Calendar;

[Dalton_Ruer]

I see your code but don't see a question. 

In general the DUAL function is ideal to create textual representations of dates that provide sorting. You get to store the alpha value so users can read it, but the numeric value you wish to use for the sort order. An example would be:

Dual( 'Month Name', Month# ) as Month

Just search for DUAL in the qlik help or check out my post on it, including a video: https://community.qlik.com/t5/Qlik-Healthcare-User-Group/You-are-invited-to-a-DUAL/gpm-p/1486276

Another easy way to provide sorting involves a deep understanding of how the Qlik symbol tables work. They keep a unique list of values in the order in which they arrive. So if you pre-build an INLINE table of your values in the sort order you want, before loading your data. You can override alpha/numeric searches and it will use the order you built the value table. 

YourField_SortOrder:

Load * Inline [

FieldName

value 1

value 2

value 3

etc.....

];

If you don't have control of the load script and you need a customized sort order you can use the MATCH function as your expression. The Match function will take your value and compare to a list of values, then return the number of the item that matches. If your field value for Month is "Feb" the following would return 2 as the result:

Match (Month, 'Jan', 'Feb', 'Mar')  

As a last resort you could petition congress to rename our months so that their order makes sense. Whoever came up with J for the first month, F for the second month, M for the third month and A for the 4'th month. Didn't they realize computers would like that? All the tips above can be used for many other things besides dates. 

[Dalton_Ruer]

Not exactly sure what you are trying to do but there is a good chance the DUAL function is what you are looking for. It allows you to retain both a TEXT and NUMERIC value. So January can be 1, February can be 2 etc. Check out the Qlik Help on the function: https://help.qlik.com/en-US/sense/September2019/Subsystems/Hub/Content/Sense_Hub/Scripting/Formattin...

The ability to add the numeric value can also come in handy for custom sorts for other types of text lists where you want values sorted in a special way like Low-Medium-High

But wait ... there's more. If you act quickly you can also use the DUAL function to create "flags" that let you dramatically improve your application performance. Instead of having to use an expression like this to find the count of all the things that were "yes" ... "SUM(If(FieldA = 'Yes', 1, 0)"  you can use the DUAL in your load script like this:

Load

...

IF(FieldA = 'Yes', Dual('Yes', 1), Dual('No', 0)) as FieldA

Now imagine you want the count of all the things that were Yes. You simply say "Sum(FieldA)" and you can still display FieldA in the friendly Yes/No manner for the end user in a filter. 

How about when you want the sum of a FieldB only when FieldA is Yes? That's just basic math ... "Sum(FieldA * FieldB)"

Tada. Pure math anything times 1 is itself and anything times 0 is 0. So you simply multiply the value you want by the 1/0 value of the "flag" you created in the load script. Your script was doing a Master Calendar and you will see Dual used a lot in master calendars for things like Weekdays, Year To Date, Month To Date etc. 

[Dalton_Ruer]

I remembered that I have a video that walks through the DUAL in relationship to performance and I've attached it here.