In previous blog
Connect Data Sources and Binding between Dataset, we share about using Connect Data Sources and binding to connect dataset. This blog will share tips:
- how to connect data sources between Date and Dimension.
- how to create list value using Date field
Use Case 1: Sales date is stored in Actual dataset, while Target amount is in Target dataset. We would like to show both actual and target in a dashboard when a certain month and year are selected.
Steps:
1. Target data
Load Year and Month as 2 fields, load both fields as Dimension, not as Measure (Number).
2. Add List
Add List for both Year and Month to the dashboard.
3. Connect Data Source for Month
For Data Source 1, select Month from Target -- here Month is Dimension. For Data Source 2, we can't select Date or type Date_Month from Actual (it is a Date field), so just select any Dimension field for temporary, in this sample, I select Sales_Rep. My dataset API name for Target is
Text_Month, and for Actual is
Sales_by_Date.
4. Edit dashboard JSON
Einstein Analytics by default will auto parse date field into many fields, e.g. FieldName_Year, FieldName_Month, FieldName_Date, and other FieldName_xxx, we can connect target Month with FieldName_Month by editing dashboard JSON, change the temporary Dimension field name Sales_Rep with Date_Month.
5. Connect Data Source for Year
From dashboard JSON, copy and paste in from Month, and change to Year as a newly connected data source.
"dataSourceLinks": [
{
"fields": [
{
"dataSourceName": "Text_Month",
"dataSourceType": "saql",
"fieldName": "Month"
},
{
"dataSourceName": "Sales_by_Date",
"dataSourceType": "saql",
"fieldName": "Date_Month"
}
],
"label": "Month",
"name": "Link_4521"
},
{
"fields": [
{
"dataSourceName": "Text_Month",
"dataSourceType": "saql",
"fieldName": "Year"
},
{
"dataSourceName": "Sales_by_Date",
"dataSourceType": "saql",
"fieldName": "Date_Year"
}
],
"label": "Year",
"name": "Link_4522"
}
]
6. The moment of Truth
Use Case 2: Target is stored as a Date field.
Steps:
1. Add List
Add List for both Year and Month to the dashboard, you cannot select Date field for List, so just select any Dimension field temporary.
2. Edit List
Edit the List and change the Bars by selecting Date then Month for Month list, and select Date then Year for Year list.
You also can manually edit dashboard JSON, looks the query and change the group to Date_Month for month list, and Date_Year for the year list.
"query": {
"groups": [
"Date_Year"
],
"measures": [
[
"count",
"*"
]
]
}
When you go back to edit the widget, notice that now it group by Date (Year) or Date (Month) for each list.
3. Connect Data Source for Month
Because you can't select Year and Month from both target and actual, just select any Dimension field as a temporary placeholder, edit dashboard JSON and change with Date_Month for both data source. Copy from Month to Year. My dataset API name for Target is Target, and for Actual is Sales_by_Date.
{
"name": "Link_2009",
"label": "Date_Year",
"fields": [
{
"fieldName": "Date_Year",
"dataSourceName": "Sales_by_Date",
"dataSourceType": "saql"
},
{
"fieldName": "Date_Year",
"dataSourceName": "Target",
"dataSourceType": "saql"
}
]
},
{
"name": "Link_2010",
"label": "Date_Month",
"fields": [
{
"fieldName": "Date_Month",
"dataSourceName": "Sales_by_Date",
"dataSourceType": "saql"
},
{
"fieldName": "Date_Month",
"dataSourceName": "Target",
"dataSourceType": "saql"
}
]
}
4. The moment of Truth
Extra: to concatenate string in SAQL, use
+
example:
'CreatedDate_Year' + "-" + 'CreatedDate_Month' + "-" + 'CreatedDate_Day'
