Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jleefjcapital
Contributor

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

Re: Date function

Do you want to see the dates like this?

Capture.PNG

Uncheck Use Continuous  Scale under Appearance.

jleefjcapital
Contributor

Re: Date function

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. 

Re: Date function

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

jleefjcapital
Contributor

Re: Date function

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

Re: Date function

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
Contributor

Re: Date function

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. 

Community Browser