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: 
jleefjcapital
Creator II
Creator II

Date function

I loaded data from an excel file, and the date  is not displaying correctly in the visualization.  I am using the master calendar as well.   

There are gaps between the dates --visualization is attached along with the QVF.

The original date is in the following format :  MM/D/YYYY

Master Calendar:

QuartersMap:

MAPPING LOAD

rowno() AS Month,

'Q' & Ceil (rowno()/3) AS Quarter

AUTOGENERATE (12);

Temp:

LOAD

min(DATE) AS minDate,

max(DATE) AS maxDate

Resident Coincident_Index;

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 DATE,

    Week(TempDate) AS Week,

    Year(TempDate) AS Year,

    Month(TempDate) AS Month,

    Year(TempDate)*100+Month(TempDate) AS MonthYear,

    Day(TempDate) AS Day,

    ApplyMap('QuartersMap', month(TempDate), Null()) AS Quarter,

    Year(TempDate)&ApplyMap('QuartersMap', month(TempDate), Null()) AS QuarterYear,

    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) AS WeekYear,

    WeekDay(TempDate) AS WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

6 Replies
sunny_talwar

Do you want to see the dates like this?

Capture.PNG

Uncheck Use Continuous  Scale under Appearance.

jleefjcapital
Creator II
Creator II
Author

Actually, I'm looking for just monthly plots, but the problem is it's being plotted on a continuous basis. It works when I just load the data without the master calendar, but when I load the master calendar it plots on a continuous basis causing the trend line to be very lumpy. 

sunny_talwar

Then don't load the master calendar if you don't need it

jleefjcapital
Creator II
Creator II
Author

The problem is I do need it to filter the dates by year and monthyear.

sunny_talwar

You don't need to create master calendar for that. You can just do that in your fact table.

FactTable:

LOAD YourFields,

          Month(DateField) as Month,

          Year(DateField) as Year,

          ...

FROM ....;

jleefjcapital
Creator II
Creator II
Author

It's part of a larger file that uses the master calendar.  I can't selectively use it for some data steps and not for others (at least I don't think).    I'm not sure what I'm doing wrong.  It works fine with other data steps that use the same data format.