topic Re: Date function in App Development

Date function

jleefjcapital — Thu, 01 Dec 2016 18:57:17 GMT

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;

Re: Date function

sunny_talwar — Thu, 01 Dec 2016 19:01:50 GMT

Do you want to see the dates like this?

Uncheck Use Continuous Scale under Appearance.

Re: Date function

jleefjcapital — Thu, 01 Dec 2016 19:16:26 GMT

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

sunny_talwar — Thu, 01 Dec 2016 19:18:02 GMT

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

Re: Date function

jleefjcapital — Thu, 01 Dec 2016 19:22:38 GMT

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

Re: Date function

sunny_talwar — Thu, 01 Dec 2016 19:24:46 GMT

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 ....;

Re: Date function

jleefjcapital — Thu, 01 Dec 2016 19:28:01 GMT

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.