Description
Creating a sorted label for the week days or months (names) in a widget is not a simple task. The base sorting is based on alphabetical order and not on the logical order of days or months.
For example (Issue) :
Friday
Monday
Saturday
Sunday
Thursday
Tuesday
Wednesday
Limitations
The following scripts works with - Column chart, Bar chart, Line chart ,Area chart and PIVOT table.
Solution
Column chart, Bar chart, Line chart ,Area chart
1. Create a custom field in the ElastiCube that will retrieve the number of day or month from the original datetime:
*The date field in the example is "ModifiedDate" and it has to be configured as Date type.
2. Kick-off an ElastiCube build and check that the new custom numerical fields (Month (1-12) or Day (1-7) ) are available to use when designing a dashboard.
3. Create a new widget based and aggregate based on the new custom numerical field - Day or Month.
4. Add the following script to the widget -
Script:
For week days -
const days = {
1: 'Monday',
2: 'Tuesday',
3: 'Wednesday',
4: 'Thursday',
5: 'Friday',
6: 'Saturday',
7: 'Sunday'
};
/*CHART*/
widget.on('processresult', function (se, ev) {
var months = ev.result.xAxis.categories;
months.forEach((month, index) => {
if (days[month] !== undefined) {
months[index] = days[month];
} else {
console.log('error');
}
});
});
For Months-
const month_desc = {
1: 'January',
2: 'February',
3: 'March',
4: 'April',
5: 'May',
6: 'June',
7: 'July',
8: 'August',
9: 'September',
10: 'October',
11: 'November',
12: 'December'
};
/*CHART*/
widget.on('processresult', function (se, ev) {
var months = ev.result.xAxis.categories;
months.forEach((month, index) => {
if (month_desc [month] !== undefined) {
months[index] = month_desc [month];
} else {
console.log('error');
}
});
});5. Click Save and browse back to the widget edit page (tab)
6. Refresh the Widget edit page
7. The changes to the the axis label should be reflected:
8. Hit Apply to save the widget
Pivot Table:
1. Create a custom field in the ElastiCube that will retrieve the number of day or month from the original datetime:
*The date field in the example is "ModifiedDate" and it has to be configured as Date type.
2. Kick-off an ElastiCube build and check that the new custom numerical fields (Month (1-12) or Day (1-7) ) are available to use when designing a dashboard.
3. Create a new PIVOT table widget based and aggregate based on the new custom numerical field - Day or Month.
4. Add the following script to the widget -
For Week Days:
var days = {
'1': 'Monday',
'2': 'Tuesday',
'3': 'Wednesday',
'4': 'Thursday',
'5': 'Friday',
'6': 'Saturday',
'7': 'Sunday'
}
widget.on('ready', function(a,b){
$.each(days, function(k,v){
$('td[fidx="0"]:contains(' + k +')', element).text(v)
});
});For Months:
On Sisense Windows:
var months_Jan_Sep = {
'1': 'January',
'2': 'February',
'3': 'March',
'4': 'April',
'5': 'May',
'6': 'June',
'7': 'July',
'8': 'August',
'9': 'September'
}
var months_Oct_Dec = {
'10': 'October',
'11': 'November',
'12': 'December'
}
widget.on('ready', function(a,b){
$.each(months_Oct_Dec, function(k,v){
$('td[fidx="0"]:contains(' + k +')', element).text(v)
});
});
widget.on('ready', function(a,b){
$.each(months_Jan_Sep, function(k,v){
$('td[fidx="0"]:contains(' + k +')', element).text(v)
});
});On Sisense Linux (Pivot 2.0 API: https://sisense.dev/reference/js/widget/pivot2.html)
widget.on('ready', function() {
$('div[class*=table-grid__content] div', element).map(function(i, cell) {
switch (cell.innerHTML ) {
case "1": cell.innerHTML='January';
break;
case "2": cell.innerHTML='February';
break;
case "3": cell.innerHTML='March';
break;
case "4": cell.innerHTML='April';
break;
case "5": cell.innerHTML='May';
break;
case "6": cell.innerHTML='June';
break;
case "7": cell.innerHTML='July';
break;
case "8": cell.innerHTML='August';
break;
case "9": cell.innerHTML='September';
break;
case "10": cell.innerHTML='October';
break;
case "11": cell.innerHTML='November';
break;
case "12": cell.innerHTML='December';
break;
case "-101": cell.innerHTML='Blank';
break;
case " ": cell.innerHTML='No Record';
break;
}
})
});5.Click Save and browse back to the widget edit page (tab)
6. Refresh the Widget edit page
7. The changes to the the axis label should be reflected:
6. Refresh the Widget edit page
7. The changes to the the axis label should be reflected:
8. Hit Apply to save the widget
