Introduction
This article will explain how use MAX() / MIN() functions on date fields using Pivot2.
Business Case
Sometimes we need to present the last / first date that a certain action occurred in or planned to.
Example
For example, we want to present when was the last sale in a specific country, or when the first task is due to begin in each project
Steps
The trick here is to create a numeric representation of the date field, and then use the MAX() / MIN() functions on this field
STEP 1 - CREATE A NUMERIC REPRESENTATION OF THE DATE FIELD
In the Elasticube manager, create a custom field and use the following expression to convert the date field into numeric field
(10000*getyear([<FIELD NAME>])+100*getmonth([<FIELD NAME])+getday([<FIELD NAME]))
STEP 2 - CREATE THE PIVOT TABLE AND USE THE MAX() / MIN() ON THE NUMERIC DATE FIELD YOU CREATED
STEP 3 - ADJUSTING THE DATE PRESENTATION
The numeric date field will be presented as a number, in order to change the display to date format, go to the widget’s script and copy paste the following code:
var dateColumns = [1,2]; //select the date columns that should be transformed. If no columns specified, then each cell will be processed
var delimiter = '.' // set delimiter symbol
var selections = "";
var mode = dateColumns.length
for (var i = 0; i < dateColumns.length; i++) {
selections += "td:nth-child(" + dateColumns[i] + ") div";
if (i < dateColumns.length - 1) selections += ",";
}
widget.on('ready', function(se, ev){
if (!mode) {
var items = $(element).find(document.getElementsByClassName('table-grid__content__inner'))
items.each(function(index, cell) {
test(cell)
})
} else {
dateColumns.forEach(function(column) {
var selector = 'table-grid__cell--col-'+column;
var items = $(element).find(document.getElementsByClassName(selector))
items.each(function(index, cell) {
test(cell)
})
})
}
})
function test(cell) {
//_.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
debugger
var num = parseFloat($(cell).text().split(delimiter).join(''));
var Year = Math.floor(num/10000);
var Month = Math.floor(Math.floor(num%10000)/100);
var Day = Math.floor(num%100);
if (!isNaN(Year) && Year >1900 && Month>0 && Month<13 && Day>0 && Day<32) {
if (mode) {
$(cell).find(document.getElementsByClassName('table-grid__content__inner')).text( ('0'+Month).slice(-2) + "/"+('0'+Day).slice(-2)+"/"+Year);
} else {
$(cell).text( ('0'+Month).slice(-2) + "/"+('0'+Day).slice(-2)+"/"+Year);
}
}
}
STEP 4 - SCRIPT CONFIGURATION
var dateColumns = [1,2]; - select the date columns numbers that should be transformed (in the example below there are 4 date columns that needs to be transformed, located in columns 1,2)
STEP 5 - SAVE THE SCRIPT, REFRESH THE PIVOT (F5) AND CLICK “APPLY”
