How to create time dimension and link to master calender

[#1]

I would like to create a time dimension and link to master calender. I'm welcome your ideas.

[#2]

I dont think, if you look all the places I'm using MM/DD/YYYY HH:MI format. Second thing whey Location data has not link with caledner.

Thanks for your time.

[#3 · Accepted solution]

I think swuehl is right: the link between the calendar and linktable is not right because the field DTTM is interpreted as text, and they have a different format (one with seconds and one without).

I hope the attached ZIP file helps you.

When you extract the 2 .qvw's in the same folder, the reload will work (I'm using binary load of the original qvw).

In newVersion.qvw you can see that it is needed to "parse" the DTTM field (with timestamp#() and timestamp() functions.

I saw that this was needed, because in the old .qvw, it is visible that those fields were NOT timestamps. In the new .qvw they are (see the screenshot in the zip-file: it has a tag like timestamp)

This file helps you to make the link from calender to the linktable.

I assume you can make the other changes yourself to tweak the calendar (I think now for every minute there is a record instead of per 15 minutes)

Please let me know if this helped.

[#4]

Thank you guys for your help. Its good information to know.

[Anonymous]

Hi All,

I am using the below script for my master calender. I have date field name as '[Settled Date]' in my data source.

But i am getting the error

(Script line error:

DataField:

LOAD

     date (+ rowno()-1) as TempDate

AUTOGENERATE

-+1).

Can any one help me?

// **************** Creating the Calendar *************



LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));

LET varMaxDate = Num(Peek('OrderDate', -1, 'Orders'));

LET varToday =num(today());



// **************** Creating the Calendar Comment*************



DateField:

LOAD

date ($(varMinDate) + rowno() -1) as TempDate

AUTOGENERATE

$(varMaxDate)- $(varMinDate)+1;





// ****************Creating the Master Calendar************

MasterCalendar:

LOAD

TempDate as OrderDate,

Day(TempDate) as Day,

WeekDay (TempDate) as Weekday,

Week(TempDate) as Week,

Month(TempDate) as Month,

Year(TempDate) as Year,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Date (monthstart (TempDate) , 'MMM_YYYY') as MonthYear,

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

inyeartodate(TempDate, $(varToday), 0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(varToday), -1) * -1 AS LastYTDFlag

RESIDENT DateField

ORDER BY TempDate ASC;



DROP TABLE DateField;

[#6]

Considered your error-message, I think the cause is that $(varMinDate) is not (correctly) evaluated as a date (but as a null or empty string instead).  So the line below that I copied from your code is incorrect.

date ($(varMinDate) + rowno() -1) as TempDate

That varMinDate is filled with this line of code:

LET varMinDate = Num(Peek('OrderDate', 0, 'Orders'));

So

question 1: does your Orders table have a valid OrderDate column?

question 2: (if answer on question 1 is 'yes'): after reload (or loading the let varMinDate) please check in the variables overview the value of varMinDate... It should be a valid date...

#Edit: since you're asking a new question, I suggest you open a new thread and refer to this discussion (with a link or something). This question is answered already. When you've done that, you could paste the link of that new topic here...   

[Anonymous]

Hi Roberto,

I have posted it in a sep que as "Error in aster Calender Script"

Que1:Its not order table. Its "Settled Date" table and this has the dates

Regards,

Nandha