How can I add reference lines for each row of a separate table to a line chart?

[jhellman757]

Hello all,

I have a  line chart that has an X axis as a continuous date field and the Y axis is the number of outstanding jobs remaining to complete the project.  The data set used to build this looks something like this:

Date	Number of Outstanding Items
1/1/2023	10
1/2/2023	8
1/3/2023	6
1/4/2023	6

and so on and so forth over a period of about 90 days.

The line chart is working well, no issues there, however I have been asked to add in reference lines for key events/milestones that are listed on a separate data table that looks like this:

Event	Date
Milestone 1	1/5/2023
Milestone 2	1/10/2023
Milestone 3	1/15/2023

My initial thought was to code in an index column and then create an individual measure for each Milestone's date. Then I would just add each reference line separately based on each measure.

The problem with that methodology is that depending on the file (this data gets refreshed daily), the number of milestone events that need to be captured as reference lines may change. One day there may be more, another day there may be less.

Can someone please advise a way forward to add this functionality? We are capable of doing data wrangling, etc. out of Qlik in Databricks if need be.

[Lisa_P]

You can load a table with an Index created as per below ..

DimRefLines:
Load RowNo() as EventNo,
     Event,
     Date as EventDate
from ....;

Then create multiple reference lines referring to the table like below.
I don't know a way to automate the process, but it will automate based on values.

Label
=Only({<EventNo={1}>}Event)

Reference Line expression
=Only({<EventNo={1}>}EventDate)

And create multiple of these.

[jhellman757]

Hello, thanks for the response! This was something we had considered, the problem is that the number of events changes from week to week. Our concern is that if we set up say 5 of these, and there is a 6th event one week we will miss it.

I suppose we could create a large enough number of them to handle this issue - however will this throw an error if it gets a bad reference? For example say we set it to EventNo = 10, and there isn't a 10th event. Will this cause an error?