Forum Discussion
Plotting 2 data sets on a timeline
I have a large dataset named "Export," which is extracted daily from an external system, containing approximately 70,000 rows and growing. Each row contains data, but the most important details for me are the date when a row was added and whether it is type A or B.
Since the date is available for each row, I was able to build a burn-up chart with the actual values. The next step was to plot the week-by-week plan. I created a separate spreadsheet with dates and planned values, week by week. For the x-axis, I used the date column from the Export dataset, and for the y-axis, I plotted both actual and planned cumulative values, which worked fine.
The relationship between the datasets was based on date-to-date, with a one-to-one cardinality and a cross-filter direction set to "both." At this point, I encountered an issue: I couldn’t see the plan beyond the last actual date, as the Export timeline ended there.
I tried using the dates from the plan for the x-axis and plotted the actual and plan on it, but the result was two flat lines. Next, I generated a separate table with a set of dates covering the entire duration, established relationships, and attempted to plot both datasets on it, but it didn’t work. I also tried various methods of aggregation, such as counting by date, week, and month. Currently, I’m using aggregation by date and type.
Vengiro you need a separate date dimension that will have relationships between both the tables and then you will use that on the x-axis to plot the values from both the actual and plan tables.
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistHi Vengiro
By the looks of it, you're not using separate dates/calendar table and instead creating a relationship directly betwen the two tables. The dates/calendar table must contain a column with unique dates that encompasses all dates in your fact tables. Create a one to many relationship from that table to your fact tables and use the date dimensions from the calendar instead in your visual.
2 Replies
Vengiro you need a separate date dimension that will have relationships between both the tables and then you will use that on the x-axis to plot the values from both the actual and plan tables.
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistHi Vengiro
By the looks of it, you're not using separate dates/calendar table and instead creating a relationship directly betwen the two tables. The dates/calendar table must contain a column with unique dates that encompasses all dates in your fact tables. Create a one to many relationship from that table to your fact tables and use the date dimensions from the calendar instead in your visual.