Get Free Trial
Get Free Trial

Format Calculated Field as Time

Serban Vasile asked on July 26, 2019

We are using a csv enabled web service as our data source, and we customized a couple of fields (prefixed header with t+) to show as hh:mm:ss.
Think of field1 as “call time” in seconds, and field2 as 1 or 0 to count it as valid or not valid record for the type of call.
We added a calculated field to find the average call time (sum (field1)/sum(field2)) and we want to display that in the same hh:mm:ss format. How do we do that? As far as we can tell, there is no UI option to do it. Can we edit the json config file to format as time?
Also, the Grand Total for the time formatted field seems to be off. How do we fix that, too?
 
Thank you in advance.

Attachments:
Clipboard01.jpg

7 answers

Public
Vera Didenko Flexmonster July 26, 2019

Hello,
 
Thank you for your question.
 
We kindly suggest formatting calculated fields as time with the customizeCell() API call.
 
Here is a JSFiddle example for illustration.
 
The reason why Grand Total results seem to be off is that the specified time format is HH:mm:s meaning that time will be represented in the 24-hour format. In order to display time data in hours even when the number of hours is greater than 24, please specify the format like this: HHH:mm:ss.
 
For more information about time formatting please see: https://www.flexmonster.com/doc/date-and-time-formatting/#!time-pattern
 
You are welcome to contact us in case any questions arise.
 
Best Regards,
Vera

Public
Serban Vasile July 26, 2019

Hi Vera,
Thank you for your prompt answer.
It’s an acceptable solution up to a certain point.
If the time pattern can be saved into the json config that allows us to customize the pivot (under the options section), the customizeCell function is NOT part of that config, which means that we require custom coding on the specific report (on the web project that builds the flexmonster web page).
You should think of adding that to the json config, the replacement customizeCell function, since it really targets that one report. That way, the json config would totally take care of the report looks, which is ultimately what you’d want from a config file.
Thank you again.

Public
Serban Vasile July 26, 2019

Also, why wouldn’t we have the custom date/time format available in the UI just like we have the number formats? And maybe other formats, regex based.

Public
Vera Didenko Flexmonster July 30, 2019

Hello,
 
 
Thank you for your reply.
Your feedback is very important to us.
 
At the moment custom formatting can be applied to calculated measures via the customizeCell() API call.
We kindly suggest using the mentioned above approach.
 
 
You are welcome to contact us in case of questions.
 
 
Best Regards,
Vera

Public
Serban Vasile August 1, 2019

I hope this helps someone, maybe even the Flexmonster team when they will add the customizeCell function to the common config (hint :)).
The only way I could pass the dynamic cellCustomize function body to flexmonster was by adding .bind(this) to the dynamic javascript function created with: new Function(‘param1’, ‘param2’ , ‘javascript code for function body’)
If you don’t do the .bind(this), typescript will not let you compile, since the signature of the dynamic java function you create with new Function() is not the same with the param of the flexmonster.customizeCell method.
My code, Angular 7, looks like below, where the customCell property holds the dynamic javascript code that I read from a database.


this.customCell=this.decodeHtml(this.customCell);
this.onCustomizeCellAlternative= (new Function(‘cell’, ‘data’, this.customCell)).bind(this);
this.pivot1.flexmonster.customizeCell(this.onCustomizeCellAlternative);

 
Again, this is needed to compensate the fact that the json config a custom report offers doesn’t contain enough to allow advanced manipulation of the cells contained in the flexmonster report.
The advanced code can be added by the customizeCell function handler. If you want to complement the json config, specific to ONE report, with the customizeCell config, persist the customizeCell javascript code on the side of the json config, and then load it dynamically as in the instructions above.
 
I am also showing below some sample templates for the customizeCell code that you might want to use:
 

customizeCellTemplate: `
//template 1
if (data.type === ‘value’ && data.measure && data.measure.uniqueName === ‘Formula #1’) {
var h = Math.floor(data.value / 3600);
var m = Math.floor((data.value – h * 3600) / 60);
var s = Math.floor(data.value – h * 3600 – m * 60);
cell.text = h + ‘:’ + m + ‘:’ + s;
}

//template 2
const hierarchy = data.hierarchy;
if (hierarchy && data.label) {
if (data.type === ‘value’) {
if (hierarchy.caption === “Supplier Name”) {
var id = JSON.parse(data.recordId);
cell.text = ‘<a href=”/’ + id.id + ‘/’+ id.parentId + \`”>\${data.label}</a>\`;
}
}
}

//template 3
if (data.type === ‘value’ && (data.label === ” || data.label === ‘[object Object]’)) {
cell.text = ”;
} else
if (data.type === ‘value’ && data.label === ‘Infinity’) {
cell.text = ‘0.00%’;
} else
if (data.type === ‘header’ && data.label === ‘[object Object]’) {
cell.text = ”;
}
if (data.type === ‘value’) {
if (data.rowIndex % 2 === 0) {
// cell.addClass(‘alter1’);
} else {
cell.addClass(‘alter2’);
}
}
if (data.type == ‘value’) {
//&& data.measure.uniqueName == ‘test’) {
if (isNaN(data.value)) {
cell.text = ‘N/A’;
}
}`

Public
Vera Didenko Flexmonster August 2, 2019

Hello, 
 
Thank you for sharing your solution with us.
 
We hope that this will be helpful to our other clients.
 
Best Regards,
Vera

Public
Serban Vasile August 2, 2019

Also, if you want to debug the dynamic code you assigned for the customizeCell function, add, as the last line in the code, this comment:

//# sourceURL=customizeCell.js

 
See attachment screenshot on how that looks like.

Attachments:
Clipboard01.jpg

Please login or Register to Submit Answer