Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
swetha54
New Contributor III

Not able to set data picker in date filter due to the date format

Hi All,

In my data, date format is like DD-MMM-YY, ex. 31-JAN-18.

I am trying to set date picker option using master calendar in date filter,but it is not working.

Kindly help me on this.

1 Solution

Accepted Solutions
shiveshsingh
Honored Contributor

Re: Not able to set data picker in date filter due to the date format

Try

Date(Date#(INV_REPORT_DATE,'DD-MMM-YY'),'DD/MM/YYYY') as INV_REPORT_DATE

11 Replies
MVP
MVP

Re: Not able to set data picker in date filter due to the date format

Please supply details about what you are trying to do. Your post is too vague to assist you.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swetha54
New Contributor III

Re: Not able to set data picker in date filter due to the date format

Sorry! 

Please find below details.

Tried with below code but some vague error is coming as field 'a' not found.

as i said in my post, date format is like DD-MMM-YY. Is this a problem, if yes can you please suggest how to convert the date format first. Thanks in advance


QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min([INV_REPORT_DATE]) as minDate,
max([INV_REPORT_DATE]) as maxDate
Resident INVENTORY_REPORT;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS [INV_REPORT_DATE],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

shiveshsingh
Honored Contributor

Re: Not able to set data picker in date filter due to the date format

When I'm trying below script, it is working.

 

INVENTORY_REPORT:
load * inline
[INV_REPORT_DATE
1/1/2018
12/1/2018];

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);


Temp:
Load
min([INV_REPORT_DATE]) as minDate,
max([INV_REPORT_DATE]) as maxDate
Resident INVENTORY_REPORT;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS [INV_REPORT_DATE],
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

 

Can you check data in your INVENTORY_REPORT table?

swetha54
New Contributor III

Re: Not able to set data picker in date filter due to the date format

Thanks Shivesh.

this code is working as INV_REPORT_DATE format is DD/MM/YYYY

But in my case the format is DD-MMM-YY. Can you please suggest how this will work for my case

shiveshsingh
Honored Contributor

Re: Not able to set data picker in date filter due to the date format

Hi

Can you share INV_REPORT_DATE data?

You can also convert date before using it in calendar.

MVP
MVP

Re: Not able to set data picker in date filter due to the date format

TempDate will be created in the default format for your environment. Do you want to change that to DD/MM/YYYY? Just add a format string:
Date($(varMinDate) + IterNo() - 1, 'DD/MM/YYYY') as TempDate

I don't see anything in that script that is sensitive to the default date format.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Not able to set data picker in date filter due to the date format

Or 'DD-MM-YYYY' as required
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
swetha54
New Contributor III

Re: Not able to set data picker in date filter due to the date format

Hi,
Thanks for your reply!
INV_REPORT_DATE format is coming like DD-MMM-YY.
i loaded this data from a file, how to convert data before using it in calendar.
MVP
MVP

Re: Not able to set data picker in date filter due to the date format

To interpret the date, use Date# during load

Date#(myDateField, 'DD-MMM-YYYY')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein