Tutorial - Using Common Date Dimensions and Shared Calendars

[rwunderlich]

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

[rwunderlich]

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]

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]

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

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[shwetagupta]

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]

Have you searched this forum for "fiscal calendar"?

[shwetagupta]

Hi 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)

[rwunderlich]

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]

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.

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]

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]

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