Is there a way to choose only the first and last string values in a table?

[MishaM]

Is there a way to choose only the first and last string values in a table?

Essentially, I need to create a title expression that shows the range of months. Also, as the months change, the data needs to update dynamically. The table I am using has a column titled Month and lists the last 4 months.

I tried using MaxString and MinString on the month column, but the data is being selected alphabetically so it is showing August as the MinString and September as the MaxString. I want it to show July as the Min and October as the Max.

I attached a small sample of the data.

[hic · Accepted solution]

If you interpret the months properly in the load script, like

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
Data:
LOAD
Month(Date#(Month,'MMMM')) as Month,
Total_Machines_Total,
Total_Machines_Win10,
Total_Machines_Win7
FROM [Sample Data.xlsx] (ooxml, embedded labels, table is Sheet1);

then you will get the sort order right, and you can use

=MinString(Month) & '-' & MaxString(Month)

[MishaM]

Hi @hic ,

Thank you for the solution! The months are in the correct order now. However, is the month is showing as the first 3 letters of the month and not the full name for all of my tables now. Is there a way to show the long month name instead?

Thanks!

Misha

[hic]

Unfortunately you cannot use the format codes in the Month function directly, so the solution is a little more complicated. The following should do the trick

Dual(Month,Month(Date#(Month,'MMMM'))) as Month,