Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In QlikSense I've been trying to sort my master calendar by most recent date first. Everything I read says it would work but it doesn't. I even did the following: saved to a qvd, loaded the qvd into a temporary calendar then load it sorted, still doesn't work.
Any idea what i'm doing wrong?
TempPolicyCalendar:
LOAD
*
FROM [lib://QVD/PolicyCalendar.qvd](qvd) ;
PolicyCalendar:
Load *
Resident tempPolicyCalendar Order By "Policy Date" DESC;
Drop table tempPolicyCalendar;
Hi
Make sure that the date is a numeric column.
Just add the date filter on sheet, if the values are on your right then its a numeric and if not then its text.
Regards,
Kaushik Solanki
Is a date considered a numeric?
yes.if you have any trouble sorting on date. you can do this.
generate a field Num(YourDateField) as SortField and do an order by on the SortField
sorry but I just noticed that once I load from the qvd into the temp calendar if I then try to sort it like above, it is no longer connected through the data model. I tested not dropping the temp calendar and the temp calendar is still connected to problem but as soon as I drop that I can no longer even see the new calendar even outside of sorting. Am I doing something wrong in the code above that it should not model it correctly?
Try it with:
TempPolicyCalendar:
LOAD
*
FROM [lib://QVD/PolicyCalendar.qvd](qvd) ;
PolicyCalendar:
Noconcatenate Load *
Resident tempPolicyCalendar Order By "Policy Date" DESC;
Drop table tempPolicyCalendar;
to create an independent table which is not automatically added to the temp-table.
- Marcus
Hi,
Most likely treating the Date as a string and not a Date,
Wrap your date in the DATE#().
i..e DATE#(Date,'DD/MM/YYYY') as Date.
But matching your field name and format.
Also as suggested earlier make a date number field then rules than out
NUM(DATE#(Date,'DD/MM/YYYY')) as DateNo
Mark
I've been playing with this and getting odd results. Based on the below resolts it doesn't look like I need to reformat the Valuation ME date2 with the DATE# as that exactly matches the format of the Valuation ME Date. I do find it interesting that I only get DateNO populated on some records. Any idea why?
Also, based on the way it's displaying in the QVD file it appears that it is actually sorted descending when it saves to QVD. However, when I use that QVD in another application I cannot get it to display descending no matter which record below I try to sort on.
Here is the code
TempCalendar:
Load
$(varMinDate) + Iterno()-1 as Num,
Date($(varMinDate)+Iterno()-1) as TempDate
AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);
ValuationCalendar:
Load
TempDate as "Valuation Date",
Date(Monthend(TempDate)) AS "Valuation ME Date",
DATE#(TempDate,'DD/MM/YYYY') as "Valuation ME Date2",
NUM(DATE#(TempDate,'DD/MM/YYYY')) as DateNo,
ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"
Resident TempCalendar
Order By TempDate DESC;
Drop Table TempCalendar;
Store ValuationCalendar into [lib://QVD/ValuationCalendar.qvd](qvd);
The results
Try it in this way:
TempCalendar:
Load $(varMinDate) + Iterno()-1 as Num
AutoGenerate 1 While $(varMinDate)+IterNo()-1 <= $(varMaxDate);
ValuationCalendar:
Load
Date(Num) as "Valuation Date",
Date(floor(Monthend(Num))) AS "Valuation ME Date",
Date(Num,'DD/MM/YYYY') as "Valuation ME Date2",
Num as DateNo,
ApplyMap('QuartersMap',month(Num),Null()) as "Valuation Quarter"
Resident TempCalendar Order By TempDate DESC;
Drop Table TempCalendar;
and here is very good explanation about: Get the Dates Right.
- Marcus
May be try
TempCalendar:
Load
$(varMinDate) + Iterno()-1 as Num,
$(varMinDate)+Iterno()-1 as TempDate
AutoGenerate 1
While $(varMinDate)+IterNo()-1 <= $(varMaxDate);
ValuationCalendar:
Load
Date(TempDate) as "Valuation Date",
Date(Monthend(TempDate)) AS "Valuation ME Date",
DATE(TempDate,'DD/MM/YYYY') as "Valuation ME Date2",
TempDate as DateNo,
ApplyMap('QuartersMap',month(TempDate),Null()) as "Valuation Quarter"
Resident TempCalendar
Order By TempDate DESC;
Drop Table TempCalendar;