powerbivega-litedeneb
Vega-lite / add dynamic layer based on range defined by joinaggregate columns
I'm building a milestone trend analysis chart with vega-lite. The vega-lite chart pulls a dataset (milestone date and finish date), calculates the range for the scales based on the subranges and plots the time series of the milestones correctly. Now I need to overlay the chart with a diagonal line and/or a triangle area in the lower right part of the chart for visually excluding the non valid part of the chart.
I tried several options:
using either milestone date or finish date series from my dataset for plotting the line / area doesn't cover the entire range of the chart:
line/area not spanning entire range
Hard coding the dates would work, however, when selecting different milestones the line / area wouldn't adapt.
When referencing parameters/signal for encoding no rule / area is displayed.
What I'm trying to achieve is this:
This is the code used for creating above visualization:
{
"width": 500,
"height": 500,
"title": {
"text": "Milestone Trend Analysis"
},
"data": {"name": "dataset"},
"transform": [
{
"joinaggregate": [
{
"op": "min",
"field": "Report Date",
"as": "Report_Date_min"
},
{
"op": "max",
"field": "Report Date",
"as": "Report_Date_max"
},
{
"op": "min",
"field": "Milestone Finish Date",
"as": "Milestone_Finish_Date_min"
},
{
"op": "max",
"field": "Milestone Finish Date",
"as": "Milestone_Finish_Date_max"
}
]
},
{
"calculate": "datum.Report_Date_min < datum.Milestone_Finish_Date_min ? datum.Report_Date_min : datum.Milestone_Finish_Date_min",
"as": "Time_range_min"
},
{
"calculate": "datum.Report_Date_max > datum.Milestone_Finish_Date_max ? datum.Report_Date_max : datum.Milestone_Finish_Date_max",
"as": "Time_range_max"
}
],
"params": [
{
"name": "Time_range_min_p",
"expr": "data('data_0')[0]['Time_range_min']"
},
{
"name": "Time_range_max_p",
"expr": "data('data_0')[0]['Time_range_max']"
}
],
"layer": [
{
"name": "milestone_trace",
"mark": "line",
"encoding": {
"x": {
"field": "Report Date",
"type": "temporal",
"axis": {
"title": "Report Date",
"grid": true,
"orient": "top"
},
"scale": {
"domainMin": {
"expr": "Time_range_min_p"
},
"domainMax": {
"expr": "Time_range_max_p"
}
}
},
"y": {
"field": "Milestone Finish Date",
"type": "temporal",
"axis": {
"title": "Milestone Date",
"grid": true
},
"scale": {
"domainMin": {
"expr": "Time_range_min_p"
},
"domainMax": {
"expr": "Time_range_max_p"
}
}
},
"color": {
"field": "Task Code",
"scale": {
"scheme": "pbiColorNominal"
}
}
}
},
{
"name": "milestone_mark",
"mark": {
"type": "circle",
"opacity": 1,
"strokeWidth": 1,
"size": 30,
"filled": false,
"tooltip": true
},
"encoding": {
"x": {
"field": "Report Date",
"type": "temporal",
"axis": {
"title": "Report Date",
"grid": true
}
},
"y": {
"field": "Milestone Finish Date",
"type": "temporal",
"axis": {
"title": "Milestone Date",
"grid": true
}
},
"color": {
"field": "Task Code",
"scale": {
"scheme": "pbiColorNominal"
}
},
"tooltip": [
{
"field": "Task Code",
"title": "Milestone Type"
},
{
"field": "Milestone Finish Date",
"title": "Finish Date",
"type": "temporal"
},
{
"field": "Report Date",
"title": "Report Date",
"type": "temporal"
}
]
}
},
{
"data": {
"values": [
{
"a": "2021-12-31",
"b": "2021-12-31"
},
{
"a": "2024-08-25",
"b": "2024-08-25"
}
]
},
"layer": [
{
"name": "date_equity",
"mark": {
"type": "line",
"strokeDash": [8, 4],
"strokeWidth": 1,
"color": "red"
},
"encoding": {
"x": {
"field": "a",
"type": "temporal"
},
"y": {
"field": "b",
"type": "temporal"
}
}
},
{
"name": "whitespace",
"mark": {
"type": "area",
"fill": "white"
},
"encoding": {
"x": {
"field": "a",
"type": "temporal"
},
"y": {
"field": "b",
"type": "temporal"
}
}
}
]
}
],
"config": {
"style": {
"cell": {"stroke": "transparent"}
}
}
}
data looks like this:
| Report Date | Milestone Finish Date | Task Code |
|---|---|---|
| 2023-07-01 | 2023-07-01 | MSFT |
| 2023-06-01 | 2023-06-20 | MSFT |
| 2023-05-01 | 2023-06-10 | MSFT |
the full dataset for producing the screenshots can be found here csv file on github
Solution
Problem was to get a data column that includes both global min and max value from both input columns ("Report Date" and "Milestone Finish Date"). This column can be then used for plotting both line and area. This column is obtained by calculate transform based on the internal dataset field "__row__" (see code)
Code is as follows:
{
"width": 500,
"height": 500,
"title": {
"text": "Milestone Trend Analysis"
},
"data": {"name": "dataset"},
"transform": [
{
"joinaggregate": [
{
"op": "min",
"field": "Report Date",
"as": "Report_Date_min"
},
{
"op": "max",
"field": "Report Date",
"as": "Report_Date_max"
},
{
"op": "min",
"field": "Milestone Finish Date",
"as": "Milestone_Finish_Date_min"
},
{
"op": "max",
"field": "Milestone Finish Date",
"as": "Milestone_Finish_Date_max"
}
]
},
{
"calculate": "datum.Report_Date_min < datum.Milestone_Finish_Date_min ? datum.Report_Date_min : datum.Milestone_Finish_Date_min",
"as": "Global_min"
},
{
"calculate": "datum.Report_Date_max > datum.Milestone_Finish_Date_max ? datum.Report_Date_max : datum.Milestone_Finish_Date_max",
"as": "Global_max"
},
{
"calculate": "datum['__row__'] <= 0 ? datum.Global_min : datum.Global_max",
"as": "Time_range"
}
],
"params": [
{
"name": "Global_min_p",
"expr": "data('data_0')[0]['Global_min']"
},
{
"name": "Global_max_p",
"expr": "data('data_0')[0]['Global_max']"
}
],
"layer": [
{
"name": "milestone_trace",
"mark": "line",
"encoding": {
"x": {
"field": "Report Date",
"type": "temporal",
"axis": {
"title": "Report Date",
"grid": true,
"orient": "top"
},
"scale": {
"domainMin": {
"expr": "Global_min_p"
},
"domainMax": {
"expr": "Global_max_p"
}
}
},
"y": {
"field": "Milestone Finish Date",
"type": "temporal",
"axis": {
"title": "Milestone Date",
"grid": true
},
"scale": {
"domainMin": {
"expr": "Global_min_p"
},
"domainMax": {
"expr": "Global_max_p"
}
}
},
"color": {
"field": "Task Code",
"scale": {
"scheme": "pbiColorNominal"
}
}
}
},
{
"name": "milestone_mark",
"mark": {
"type": "circle",
"opacity": 1,
"strokeWidth": 1,
"size": 30,
"filled": false,
"tooltip": true
},
"encoding": {
"x": {
"field": "Report Date",
"type": "temporal",
"axis": {
"title": "Report Date",
"grid": true
}
},
"y": {
"field": "Milestone Finish Date",
"type": "temporal",
"axis": {
"title": "Milestone Date",
"grid": true
}
},
"color": {
"field": "Task Code",
"scale": {
"scheme": "pbiColorNominal"
}
},
"tooltip": [
{
"field": "Task Code",
"title": "Milestone Type"
},
{
"field": "Milestone Finish Date",
"title": "Finish Date",
"type": "temporal"
},
{
"field": "Report Date",
"title": "Report Date",
"type": "temporal"
}
]
}
},
{
"name": "date_equity",
"mark": {
"type": "line",
"strokeDash": [8, 4],
"strokeWidth": 1,
"color": "red"
},
"encoding": {
"x": {
"field": "Time_range",
"type": "temporal"
},
"y": {
"field": "Time_range",
"type": "temporal"
}
}
},
{
"name": "whitespace",
"mark": {
"type": "area",
"fill": "white"
},
"encoding": {
"x": {
"field": "Time_range",
"type": "temporal"
},
"y": {
"field": "Time_range",
"type": "temporal"
}
}
}
],
"config": {
"style": {
"cell": {"stroke": "transparent"}
}
}
}
Solution looks like this
