Is there way to add text to field showing the range of the values of another column?

Question
1

One of columns is DATE. I would like to create another column COMMENT and there include text:

“Range from MIN(DATE) - MAX(DATE)”.

MIN(DATE) would be the oldest date in the column DATE.
MAX(DATE) would be the most recent date in the column DATE.

Then I would use a Summary function to total a value in another column, e.g. column FEES.

All I am looking for is to see table something like this… Col 1 is COMMENT and Col 2 is FEES.

COMMENT: ____________________________________________ FEES
Range from 1.4.2022 - 31.03.2023 ________________ 3999

I tried Fill function but that only works if the adjacent cells are empty. I tried various Max Min formulas but they don’t work on dates. I looked in the daytime formula if there is a way to convert the date to a universal time number so that I can use Min Max and then convert it back to time, but no, no such function.

2

The Stats transform doesn’t work on dates.

Summary shows the max and min dates. But you might need to use DateTime Format to convert it to something useful.

Or

You can use Unique if you have (or add using New Col) a key column with a key value that is the same for each row where you want to find the min/max date.

Or

Sort then use Slice.

This .transform shows all 3 methods to get the maximum date.

max-date.transform (4.9 KB)

image
image966×648 79.6 KB

Once you have the maximum and minimum you can use Join, Cross etc to merge with the original dataset.

1 Like
3

I tried Summary but didn’t see that check-box there to indicate the field is a date.

Good. Summary + Cross is the most elegant way to do it for me. Works.

1 Like
4

Searching through 1000s of values to try to work out if a column is dates (and what date format) is quite slow for big datasets. That is why it is optional.

1 Like