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

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

-Rob

Not applicable

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.Diagram.png

Not applicable

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

Capture.JPG.jpg

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

The fieldname parameter should be quoted, no brackets.

CALL Qvc.CalendarFromField('Settlement Date');


-Rob

Not applicable

Thanks Rob!

Not applicable

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

Capture.JPG.jpg

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author

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

Not applicable

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

Not applicable

Interesting topic, however link is broken.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP
Author