Qlik Community

QlikView Documents

Documents for QlikView related information.

Linking to two or more dates

MVP & Luminary
MVP & Luminary

Linking to two or more dates

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

Labels (2)
Attachments
Comments
MVP & Luminary
MVP & Luminary

@ritaaguiar Can you just click ok and then reload?

-Rob

0 Likes
ritaaguiar
Contributor

@rwunderlich

By Clicking ok and then reload you mean trying to open it again? Yeah, but it shows that message again.

0 Likes
MVP & Luminary
MVP & Luminary

@ritaaguiar Are you using Personal Edition or a licensed version?

-Rob

0 Likes
ritaaguiar
Contributor

@rwunderlich

Personal edition, why you asking?

0 Likes
MVP & Luminary
MVP & Luminary

The embedded license allowed PE users to open the qvw.  However, the embedded license has expired and I no longer have the capability to embed PE licenses. I've updated the copy on the website to remove the embedded license. Please re-download. As you are using Personal Edition, you may have to use up one of your recoveries to open it.  Reload after recovery.

-Rob

0 Likes
Samir
New Contributor

Hello Rob,

I am following your instruction and I was able to create a DateLink table but I am getting error when I include Call CalendarFromField('Date', 'CommonCalendar',''); CalendarFromField.JPG in the script. It doesn't seem to find CalendarFromField function.

Can you please help?

Thanks

Samir

0 Likes
MVP & Luminary
MVP & Luminary

Did you include the Sub code from the sample script?

-Rob

0 Likes
StaceyR
New Contributor

Hi Rob,

Thank you for posting this tutorial. Your tutorial helped me to rework my method.

I was wondering if the common calendar month can be used in an expression?

I am creating an end-of-month inventory summary across all months. I have a table with an item’s IN and SOLD dates. To count inventory, an item would have to be available on or before a given month "MonYr"  and sold during a subsequent month.

   In one of my trials building the inventory QV I had created a variable to select a desired inventory month, “MonYr”. This worked fine in the set analysis comparison (Sum({<[In MonYr] ={"<=MonYr"}, [Sold MonYr] ={">MonYr"}>} Counter)), but it didn't work as a dimension.

Using your template, the common MonYr worked well as the dimension but not in the expression.

Manually selecting each month across time is not a practical option for me. But it might be my only option.

Thank you!

So for example, item #1020 would tally in Nov, Dec, Jan end-of-month  inventory.

#1183 in Oct &Nov inventory

#1566 in Feb-May inventory

#1702 Dec+ inventory

ItemIDDateInDateSoldRegion
102011/30/20182/28/2019A
118310/11/201811/11/2018C
134410/21/201812/11/2018A
142910/14/201812/30/2018A
143710/18/201811/15/2018B
15662/17/20195/17/2019D
168710/12/20181/7/2019A
169010/17/201811/19/2018D
170212/24/2018 A
19321/6/20191/26/2019C
20902/10/20194/23/2019B
20981/15/20191/16/2019D
21042/6/20193/20/2019D

 

0 Likes
MVP & Luminary
MVP & Luminary

For this type of problem I believe you'll want to use an AsOf table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

-Rob

Version history
Revision #:
1 of 1
Last update:
‎2014-02-07 02:44 PM
Updated by: