How To Calculate The Maximum / Minimum Date Per Categoty
Introduction
This article will explain how use MAX() / MIN() functions on date fields
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 = [2,3,4,5]; //select the date columns that should be transformed
var selections = "";
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){
var e = element;
var w = widget;
var d = dashboard;
_.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
var num = parseFloat($(cell).text().split(',').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)) {
debugger;
$(cell).text( Day + "/"+Month+"/"+Year);
} else {
$(cell).text('');
}
})
})
Step 4 - Script Configuration
var dateColumns = [2,3,4,5]; - 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 2,3,4,5)
Step 5 - Save the script, refresh the pivot (F5) and click “apply”
How to add a custom fields:
MaxMinDate.ecdata
MaxMinDate.dash
-
Hopefully it will be implemented soon.
Till then this workaround needs to be used.
Below you'll find a improvement to the script above so other localizations can be supported too.
var thousand_separator = '.' //replace . with your local thousands separator
var date_separator = '-' //replace - with your local date separator
var dateColumns = [2,3,4]; //select the date columns that should be transformed
var selections = "";
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){
var e = element;
var w = widget;
var d = dashboard;
_.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
var num = parseFloat($(cell).text().split(thousand_separator).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)) {
debugger;
$(cell).text( Day + date_separator + Month + date_separator + Year );
} else {
$(cell).text('');
}
})
}) -
Hi, thanks... it's working :)
would be nice if you could help with the possibility to see a day diff between max and min dates (e.g. day difference between first game date and last game date).
Is there any supportive script?
Off course, out of the box solution will be much appreciate.. any updates on that?
Sahar
-
If you'd like to apply this method on an Indicator widget use the following code:
widget.on('ready', function(se, ev){
setTimeout(function(){
var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
var Year = Math.floor(num/10000);
var Month = Math.floor(Math.floor(num%10000)/100);
var Day = Math.floor(num%100);
$($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
}, 0);
}); -
Thanks for the response
If it applies for an indicator widget only then it might be helpful for other (very specific) things, but will not be relevant for my example.
Nonetheless, If i do want to use it, where should i paste it, below/above/instead of the original script?
Thanks,
Sahar
-
If anybody is trying to use this with an embedded indicator widget, code needs a slight adjustment:
widget.on('ready', function(se, ev){
setTimeout(function(){
var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
if(!num)
num = $$get(ev, 'widget.queryResult.value.data');
var Year = Math.floor(num/10000);
var Month = Math.floor(Math.floor(num%10000)/100);
var Day = Math.floor(num%100);
$($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
}, 0);
}); -
Thanks to Omer - New script that works with a few categories.
var m_names = new Array("Jan", "Feb", "Mar",
"Apr", "May", "Jun", "Jul", "Aug", "Sep",
"Oct", "Nov", "Dec");
var dateColumns = [4]; //select the date columns that should be transformed
var num = 0;
var selections = "";
widget.on('ready', function(se, ev){
$.each(dateColumns, function(index, value){
num = $("tbody tr:first").children().length - value;
var e = element;
var w = widget;
var d = dashboard;
$("tbody tr" , e).not('.wrapper, .p-head-content').each(function(){
var cell = $(this).children().last();
for (var a = 0; a < num; a++){
cell = cell.prev();
}
var num1 = parseFloat(cell.text().split(',').join(''));
var Year = Math.floor(num1/10000);
var Month = Math.floor(Math.floor(num1%10000)/100);
var Day = Math.floor(num1%100);
console.log(isNaN(Year));
if (!isNaN(Year)) {
var final_date = m_names[Month-1] +"-"+Day + "-" + Year;
cell.text(final_date);
} else {
cell.text('');
}
})
});
}); -
Hi Alon,
Follow all the steps as mentioned,but unexpected result.
What can be the issue.
Find the steps below:
Custom Field query :
(10000*getyear([<FIELD NAME>])+100*getmonth([<FIELD NAME])+getday([<FIELD NAME]))
Widget Script :
var dateColumns = [2]; //select the date columns that should be transformedvar selections = "";
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){
var e = element;
var w = widget;
var d = dashboard;_.each($(selections , e).not('.wrapper, .p-head-content'), function(cell){
var num = parseFloat($(cell).text().split(',').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)) {
debugger;
$(cell).text( Day + "/"+Month+"/"+Year);
} else {
$(cell).text('');
}
})
})Result ->
Regards
Tripti GE
-
Hi there,
Love this workaround. Very useful.
Is there any way to quickly use filter according to first date at the dashboard level? I've created the numeric date field, and the filter works great for specific pivot tables and widgets. However, if I have a dashboard with several pivot tables, it's a bit tedious to have to update the widget filter for every single widget in the dashboard.
Thanks,
Jimmy
-
Hi,
This workaround is great - however it seems that the indicator script is not working for 6.7 , any chance it can be updated? I am using the scripts from Dotan's comment.
widget.on('ready', function(se, ev){
setTimeout(function(){
var num = $$get(ev, 'widget.rawQueryResult.values.0.data');
var Year = Math.floor(num/10000);
var Month = Math.floor(Math.floor(num%10000)/100);
var Day = Math.floor(num%100);
$($('.number_span', element)[0]).text( Day + "/" + Month + "/" + Year);
}, 0);
});Thanks,
Alina
-
I also vote that this should be supported as native functionality in Sisense. In my use case, I want to take the max(date) for a category in a table. I don't think I'll make the effort to try to use all of the various scripted workarounds knowing that the next upgrade is likely going to break whatever workaround we put in place.
-
There is another way if you don't want to use that technique.
- Add the date in values and use this formula there
min(DDiff(now(<Date Column>),<Date Column>))
Note: This will give you latest date. If you want the earliest use max instead of min - Make sure to Group by days in the above columns.
- Then use this script
var dateColumns = [13,14]; //select the date columns that should be transformed (count from 0)
var num = 0;
var selections = "";widget.on('ready', function(se, ev){
$.each(dateColumns, function(index, value){
num = $("tbody tr:first").children().length - value;
var e = element;
var w = widget;
var d = dashboard;
$("tbody tr" , e).not('.wrapper, .p-head-content').each(function(){
var cell = $(this).children().last();
for (var a = 0; a < num; a++){
cell = cell.prev();
}
var num1 = parseFloat(cell.text().split(',').join(''));
var d = new Date();
d.setDate(d.getDate()-num1);
cell.text(d.toDateString());
if(isNaN(num1)){
cell.text("");
}
})
});
}); - Dont ask me how the script works :p. I just edited the script by Yoni Lerner. I'm sure the people can optimize it in their own way.
- Add the date in values and use this formula there
-
If you are trying to do the same thing in Pivot table on Linux, here is the script (that uses Pivot 2 APIs)
widget.transformPivot({
type: ['value']
}, function(metadata, cell) {
var value = cell.content;
var num = parseFloat((value).split(',').join(''));
var Year = Math.floor(num/10000);
var Month = Math.floor(Math.floor(num%10000)/100);
var Day = Math.floor(num%100);
var columnToConvert = 2;
if (metadata.colIndex == columnToConvert && value.length){
cell.content = ( ('0'+Month).slice(-2) + "/"+('0'+Day).slice(-2)+"/"+Year );
}
});
Please sign in to leave a comment.
Comments
23 comments