const { data, widget } = payload;
const { selectedDate, fromDateDim, toDateDim } = data;
if (!(selectedDate && fromDateDim && toDateDim)) {
return;
}
const fromFilter = widget.dashboard.filters.item(true, fromDateDim);
const toFilter = widget.dashboard.filters.item(true, toDateDim);
if (!(toFilter && fromFilter)) {
return;
}
setNewDatesInFilters(fromFilter, toFilter, selectedDate);
widget.dashboard.refresh();
function setNewDatesInFilters(fromFilter, toFilter, newDate) {
toFilter.jaql.filter = {
from: newDate
};
fromFilter.jaql.filter = {
to: newDate
};
updateFilter(toFilter.jaql);
updateFilter(fromFilter.jaql, true);
}
function updateFilter(filterObject, save = false) {
widget.dashboard.filters.update({
jaql: filterObject
}, {
save,
refresh: false
})
}
Snippet of this action:
{
"type": "betweenDates",
"title": "Apply",
"data": {
"selectedDate": "",
"fromDateDim": "",
"toDateDim": ""
}
}
Let’s use this action in the BloX. Also, we will add the element “Input.Date”. As the result, we will get the following BloX JSON:
{
"style": "",
"script": "",
"title": "",
"showCarousel": true,
"body": [
{
"type": "Container",
"items": [
{
"type": "Input.Date",
"id": "data.selectedDate",
"class": ""
}
]
}
],
"actions": [
{
"type": "betweenDates",
"title": "Apply",
"data": {
"selectedDate": "",
"fromDateDim": "[History.From (Calendar)]",
"toDateDim": "[History.To (Calendar)]"
}
}
]
}
Now, you can choose a date and after clicking “Apply” both filters will be updated with the selected date. The only issue - input field is cleared. We will fix this by adding a simple widget’s script to the widget BloX - this script will persist the selected date in the input field:
widget.on('ready', () => {
const toFilter = widget.dashboard.filters.item('[History.To (Calendar)]');
const selectedDate = $$get(toFilter, "jaql.filter.from");
if (selectedDate && document.getElementById('data.selectedDate')) {
document.getElementById('data.selectedDate').value = selectedDate;
}
})
Now, when the dashboard is loaded, the script finds the previously selected date and fills in the input field. Also, when a user changes the date, the newly input date is shown.
Let’s solve the second challenge to show statuses of the record on the given date. Let’s assume that we have table “Cases”. In this table we have historical information about cases:
- Id - unique identifier of the record;
- CaseId - case’s identifier;
- Status - status of the case;
- Date - date when case status was changed.
Sample of the data in this table:
[ALT text: A table displaying case information with the following columns: Id, CaseId, Status, and Date. The table includes rows showing various case entries with status updates such as "New," "Open," "Reopen," and "Closed," along with corresponding timestamps for each case.]
Use-case: as an end-user, I want to input the date and Sisense should return the status of the case in the given date. To accomplish this, let’s create a new custom table “History”, where we will compute the first date of the status and its last date. This custom table will be populated with the next SQL query:
SELECT CaseId, [Old Status], [New Status], [From], [To] FROM (
SELECT c.[CaseId], c.[Status] "Old status", cc.[Status] "New status", c.[Date] "From", cc.[Date] "To", RankCompetitionAsc(c.CaseId, c.[Status], c.Date, cc.Date) "Rank" FROM Cases c
INNER JOIN Cases cc
ON c.[CaseId] = cc.[CaseId] and c.date < cc.date
) c
WHERE c.Rank = 1
UNION
(
SELECT c.[CaseId], c.[Status] "Old Status", '' "New status", c.date "From", CreateDate(2999, 12, 31) "To" FROM Cases c
INNER JOIN (
select caseId, max(date) "MaxDate" from cases
group by caseId
) latestStatus
ON c.[CaseId] = latestStatus.caseId and latestStatus.MaxDate = c.date
)
This query contains two parts:
- The first part returns statuses, which were already changed and we have information about the status after the change;
- The second part shows the current status of the record.
As a result of the data transformation, we will have the following structure:
[ALT Text: A table displaying status updates with columns labeled "CaseId," "Old Status," "New Status," "From," and "To." There are five rows of data showing various cases, their old and new statuses, and corresponding timestamps for when the status changes occurred. The "From" and "To" columns indicate the date and time of the changes. Some cases are marked as "New," "Open," "Closed," and "Reopen."]
Now, we will use approach from the first part of this article to create dashboard’s filters and utilize BloX to input the date and custom action to update the dashboard’s filters.
Conclusion
In this article we reviewed a possible way to manipulate date filters. A similar approach you can use for embedding cases. Since the filters will be controlled from the BloX widget and filters will be locked for manual editing, you can even hide these dates filters using the following plugin:
https://www.sisense.com/marketplace/add-on/hide-filters
Similar Content:
Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this please let us know.
