cancel
Showing results for
Did you mean:

# How To Calculate The Maximum / Minimum Date Per Category On Pivot2

Community Team Member
Introduction
This article will explain how use MAX() / MIN() functions on date fields using Pivot2.
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 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 += ",";
}

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) {
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”

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
Community Toolbox

Developers Group:

Product Feedback Forum: