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.
Hey Rob, Thanks for the resource. This looks like a great solution to a common problem on paper. However, have you ever ran into situations where this caused too much ambiguity for the end user in the final application or created issues with set analysis calculations as you were developing the application?
Hi Michael,
I've not run into any problems of ambiguity or analysis problems. Before I implement the pattern, I discuss with the user what type of analysis they want. I am implementing the users specification, so there is typically no confusion as to what a "date" means. The issue has already been discussed.
Hello Rob,
Thanks for this tutorial, is very well explained and has been very helpful.
I would like to make you a question regarding this, I have ran into this new situation in which I have to build a chart that shows a payment schedule for a Real Estate company.
This would be a sample of the data for only one unit:
Unit | Planned Date | Amount | Payment Date | Amount Paid |
201 | 6/12/2013 | 1,445.4 | 6/12/2013 | 1,445.4 |
201 | 7/1/2014 | 30,030.9 | 12/1/2014 | 25,526.0 |
201 | 7/2/2014 | 8,116.5 | 9/2/2014 | 8,116.5 |
201 | 7/3/2014 | 8,114.5 | 8,114.5 | |
201 | 7/4/2014 | 8,109.5 | 8,109.5 | |
201 | 7/5/2014 | 8,112.6 | 8,112.6 | |
201 | 7/6/2014 | 8,114.6 | 8,114.6 |
As in your example, I have to use both date fields in my chart to make an analysis of the payment's status for each unit, but as you will see I have many rows for each unit. How should I handle this particular scenario?
Thank you in advance,
You will need to have a unique transactionId for each row. The transactionId field will be the link between the date tables(s) and the fact table.
-Rob
Rob,
Thanks a lot the example. I'm now in a situation like this and it will happen a lot.
Any way to get the license in the attached QVW updated? I tried loading it but get an error message saying "The license that is embedded in this document is not valid".
Thanks for excellent code. Just wonder trying to understand the function CalendarFromField. I am new to Qlikview but not to programming but fail to understand the sequence of execution of this function.
For example how the following load works when DateMin and DateMax is not yet defined, which defined later in the script
// Generate range of dates between min and max.
LOAD
date(DateMin + IterNo()) as [$(_field)] // Link Field
WHILE DateMin + IterNo() <= DateMax
;
Such here and here also datefield is not defined, which is defined later
LOAD
min(datefield)-1 as DateMin
,max(datefield) as DateMax
;
Also the last statement is, which is generating the datafield, so request if someone can explain how it being working and eventually the main table is populated.
LOAD
FieldValue('$(_field)', RecNo()) as datefield
AutoGenerate FieldValueCount('$(_field)');
Thanks. It will help me understand the function of load and script execution. Thanks.
Thanks. I finally got how preceding load works. Thanks for wonderful code.
Thank You Very Much rwunderlich