cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member
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”

Rate this article:
(1)
Comments
gayatrishahane
8 - Cloud Apps
8 - Cloud Apps

How to make the script work for an aggregated table instead of a pivot table?

Version history
Last update:
‎03-02-2023 09:09 AM
Updated by:
Contributors