Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Cannot get master calendar to sort ascending

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;

12 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Anonymous
Not applicable
Author

Is a date considered a numeric?

krishna_2644
Specialist III
Specialist III

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

Anonymous
Not applicable
Author

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?

marcus_sommer

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

Mark_Little
Luminary
Luminary

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

Anonymous
Not applicable
Author

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

Capture.PNG

marcus_sommer

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

sasiparupudi1
Master III
Master III

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;