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: 
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Tutorial - Using Common Date Dimensions and Shared Calendars

The question of aggregating multiple dates into a common date dimension like Month comes up frequently on this forum. I've posted a QVW tutorial on my website that shows how I typicaly handle the issue.

http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions

The QVW is not Personal Edition enabled.

-Rob

Message was edited by: Rob Wunderlich Link updated

65 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

I would add a loop around the calendar load like:

FOR hour = 0 to 23

LOAD ,..

     TimeStamp(Date + MakeTime($(hour))) as DateTime.

NEXT hour

shwetagupta
Partner - Creator II
Partner - Creator II

Hi ,

I was following your way to create Link Table for common calendar.

Need your help in converting this Calendar to fiscal calendar.

I am not getting the way to convert it though I have followed many other blogs since I am pretty bad with dates and calendar codes.

I am stuck in my project just because of this. Please Help.

Any help will be appreciated.

My scenario includes two date fields from  different tables.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

Can you post the script you have tried? Or even better, a QVW?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

shwetagupta
Partner - Creator II
Partner - Creator II

Hi Rob

Thanks for reverting. I dont have access to my server right now but my script looks exactly same as that of yours.

Secondly i am working on qlik sense.

It will be great help if you suggest changes to be done in your script to make it a common fiscal calendar.

My fiscal months starts from April.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

Have you searched this forum for "fiscal calendar"?

shwetagupta
Partner - Creator II
Partner - Creator II

common calendar 2.pngHi Rob,

Though I have converted my calendar to fiscal calendar but I am not getting desired result , having a doubt in the qvw you posted in the tutorial.

If I ll select any date field in DateLink wont it hit the other dates ?

Because when i am trying to validate my model I selected date , it should highlight the invoice date as well if date is available in invoice date field.(as mentioned in second screen shot)

common date.png

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

In the tutorial, all dates in the range are generated for all four calendars.  The field that links the actual activities is OrderId.  For example, there will only be a linkage between Date (in the Common Calendar) and dates in the other calendars when there was an actual Order activity on that date.  try selecting Date=2/1/2012.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

femidadzie
Contributor II
Contributor II

Hey Rob,

first of all thank you for your tutorial , it is a big help.

I implented your code into my Script and besides one thing everything is fine.

For my Report I need the Week numbers, the problem is, when I select for Examble

week '20' in the common date calendar, I get week '20' to '23' (see field 'StatusDate Week').

The Week is refering to the field 'StatusDate'.

I can't find the reason why I only get '20' back in field 'Week',  even if the Date is clearly not

in that week.

On the other Hand if I choose In the optional Fact Calender week '20' I get the wished result.

WeekProblem.png

In the Attachement you can find my datamodel and Script.

I appriciate any help, im dealing 2 days now with this case.

Thanks in advance

and

Best regards

Femi

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

It would be much easier to help if you cold attach a QVF. Selecting Week 20 selects OrderIds that have either a ProdWeek or a StatusWeek of 20.  So it makes sense that some OrderIds in ProdWeek 20 have StatusWeeks in other weeks, yes?

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

femidadzie
Contributor II
Contributor II

Hi Rob,

that is right.

In the production table (ProdWeek) there are only Orders that are already closed.

In the BDE table  (StatusWeek) there are orders which are closed and orders which are still in progress.

Im pretty sure i had been to unspecific.

My real Problem is that when i select Week 25 in the common calendar and use the Sum function, i get a wrong output.

The source data gives me, for week 25, a performance time of 11.965 h back. So my thought was, if I select week 25

in my common calendar, the result should be equal to that, but i get 21,06 h back and i have no clue why.

Even when I used Join (right, left, outer or inner) i didn't got the right result.

I looked it up and thought a reason could be, that the Date in my common calendar which the Week is referring to, doesn't match. If I choose week 25 or 20 in my common calendar there should be only Dates (in the common calendar) which are within my query.

I made an Example. QVF

thanks for your help.

-Femi