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]

Take out the expressions from the sample and it appears to work fine.

-Rob

[#3]

Hi, It’s very nice post about Common date dimensions across all the fact and dimensions in Qlikview, I have similar requirement in my assignment and trying to implement. My requirement is, I have a Member Fact, Engagement Dimension and may more Dimension.  I need to create two Chart/Pivot Table one for Member Join Date and one is for Member Engagement Date, I need to provide a common list box for both the Chart/Pivot Table to the customer, when customer select any one YEAR, Quarter, Month or Date from list box, all the data should get changes for both the Chart/Pivot Table. I already created DateLink and CommonCalendar objects as you suggested, but that is not working for me, when I take the data in table view and look into that, I can find there are members/engagement who are not assigned to any year or quarter or month. Can you please help me on that? I am also posting my Table View diagram and QVW.

[#4]

Hi Rob,

Im pretty new to QV. I just discovered your calendar solution which seems great. However when trying to implement into my script I'm getting an error message as per below. Any clues?

Thanks in advance,

Olle

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

$(Must_Include=C:\Users\xxxxx\Desktop\qvc-8.2\QlikviewComponents-8.2\Qvc_Runtime\Qvc.qvs);

Directory;
LOAD
     [Account number],
     [Settlement Date],
     [Settlement Time]

FROM

(biff, embedded labels, table is [Transaction log$]);

CALL Qvc.CalendarFromField([Settlement Date]);

[rwunderlich]

The fieldname parameter should be quoted, no brackets.

CALL Qvc.CalendarFromField('Settlement Date');

-Rob

[#6]

Thanks Rob!

[#7]

Hi again Rob,

your calendar works great.

However, _WeekSerial is giving me first week in jun as week 1, instead of week 23 as per below.

Any ideas?

Thanks in advance,

Olle

[rwunderlich]

Hi Olle,

_WeekSerial is a relative counter field, not the week number of the year. _WeekSerial 1 represents the first week of your data.

_WeekSerial (and the other *Serial fields) are used to simplify computing n periods before/after.

-Rob

[#9]

Hi Rob,

thanks fo your answer.

Is there a simple way for me to get the week number of the year figures? Still using your calendar solution that is.

Thanks in advance,

Olle

[#10]

Interesting topic, however link is broken.

[rwunderlich]

Don Callaway Thanks for the heads up. The new link is posted above.

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

-Rob