Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Linking to two or more dates

cancel
Showing results for 
Search instead for 
Did you mean: 
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Linking to two or more dates

Last Update:

Feb 7, 2014 2:44:13 PM

Updated By:

rwunderlich

Created date:

Feb 7, 2014 2:44:13 PM

Attachments

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

@ritaaguiar Can you just click ok and then reload?

-Rob

0 Likes
ritaaguiar
Creator
Creator

@rwunderlich

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

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob

0 Likes
ritaaguiar
Creator
Creator

@rwunderlich

Personal edition, why you asking?

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Did you include the Sub code from the sample script?

-Rob

0 Likes
StaceyR
Contributor
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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

ioannagr
Creator III
Creator III

Hello @rwunderlich , excuse my direct approach, but I believe you may be able to help me. I want to link new dates coming from two different tables but once belonged in the same table.

How is this possible?

I have split the source table for each attribute of field "type" and now I have date_for_type_1 in table Type1, date_for_type_2  in table Type2, etc. Each key field in each one table of them may have multiple date_for_type_1s or/and date_for_type_2s.

 

For example in a filter, i can't use just "date" anymore.

 

Any help is appreciated 🙂

0 Likes
Version history
Last update:
‎2014-02-07 02:44 PM
Updated by: