Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

czehnder
New Contributor

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;

Tags (1)
12 Replies

Re: Cannot get master calendar to sort ascending

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

czehnder
New Contributor

Re: Cannot get master calendar to sort ascending

Is a date considered a numeric?

krishna_2644
Valued Contributor III

Re: Cannot get master calendar to sort ascending

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

czehnder
New Contributor

Re: Cannot get master calendar to sort ascending

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?

Re: Cannot get master calendar to sort ascending

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

mark6505
Valued Contributor III

Re: Cannot get master calendar to sort ascending

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

czehnder
New Contributor

Re: Cannot get master calendar to sort ascending

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

Re: Cannot get master calendar to sort ascending

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
Honored Contributor III

Re: Cannot get master calendar to sort ascending

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;