asked on April 29, 2020
Hello, is any possible way to use aggregation functions such as min or max with a date with type "year/month/day"? Running ahead, I want to say that changing date type is not an option, because grouping by date part is required. There is an example. Thank in advance.
⋅ April 29, 2020
Hi Sergii,
Thank you for writing to us.
Please note that the year/month/day date-type fields are mainly designed to be used as rows/columns members instead of being displayed as measures. Therefore, aggregations like max, min etc. are not applicable to this field type.
Taking into account that changing the field type is not feasible in your situation, we would suggest adding a separate column which would duplicate the dates from the year/month/day field, but in the date string or datetime format instead, which would allow you to apply the desired aggregations to this field.
We've prepared a simple JSFiddle example based on your sample to illustrate this approach: https://jsfiddle.net/flexmonster/s89en42x/
Please let us know if this helps.
Best regards,
Mykhailo
⋅ April 30, 2020
Hello Mykhailo. Thank you for the response and example. But what should be done in the case when more than one aggregation needed by the same field? For example min date and max date, please have a look. P.S. On the glance it works well, but if you start playing with date field through UI(e.g. change aggregation type), one of the 'grantTotalCaption' will be lost. It looks like, having more than one measure with the same 'uniqueName' is a bad idea. Thanks in advance.
⋅ May 1, 2020
Hi Sergii,
In case you prefer to display different date aggregations as separate measures with different captions, we would suggest using calculated values and defining their formulas correspondingly:
{
caption: "First visit",
uniqueName: "firstvisit",
formula: "min('date agg')"
},
{
caption: "Last visit",
uniqueName: "lastvisit",
formula: "max('date agg')"
},
However, passing the date field to a calculated value transforms the field member to the UNIX timestamp format, which means that some kind of postprocessing needs to be implemented in order for the value to be human-readable. This can be achieved by using the customizeCell Flexmonster API call together with the toLocaleDateString() JavaScript method:
function customizeCellFunction(cell, data) {
if (data.measure != undefined &&
(data.measure.uniqueName == 'lastvisit' || data.measure.uniqueName == 'firstvisit') &&
!isNaN(data.value)) {
cell.text = new Date(data.value).toLocaleDateString();
}
}
You can also specify a custom function that formats the date.
We've prepared a quick JSFiddle sample illustrating the described approach: https://jsfiddle.net/flexmonster/6Lbohpz9/
We hope this helps!
Regards,
Mykhailo