Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@ritaaguiar Can you just click ok and then reload?
-Rob
By Clicking ok and then reload you mean trying to open it again? Yeah, but it shows that message again.
@ritaaguiar Are you using Personal Edition or a licensed version?
-Rob
Personal edition, why you asking?
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
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',''); in the script. It doesn't seem to find CalendarFromField function.
Can you please help?
Thanks
Samir
Did you include the Sub code from the sample script?
-Rob
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
ItemID | DateIn | DateSold | Region |
1020 | 11/30/2018 | 2/28/2019 | A |
1183 | 10/11/2018 | 11/11/2018 | C |
1344 | 10/21/2018 | 12/11/2018 | A |
1429 | 10/14/2018 | 12/30/2018 | A |
1437 | 10/18/2018 | 11/15/2018 | B |
1566 | 2/17/2019 | 5/17/2019 | D |
1687 | 10/12/2018 | 1/7/2019 | A |
1690 | 10/17/2018 | 11/19/2018 | D |
1702 | 12/24/2018 | A | |
1932 | 1/6/2019 | 1/26/2019 | C |
2090 | 2/10/2019 | 4/23/2019 | B |
2098 | 1/15/2019 | 1/16/2019 | D |
2104 | 2/6/2019 | 3/20/2019 | D |
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
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 🙂