Max of String (sorted in alphabetical order) in a calculated field

Hi All,

is there a way to calculate the max of a String, based on his alphabetic order?

In a table functions we can insert the string field inside the brackets, eg. [{string dimension} ASC]. I would like to have the same but with a function, actually I cannot use Max function with a string.

Thanks

You could use two custom calcs together.

Example using Superstore data below.

  1. Rank to get the last alphanumerical value (used for filters, etc)
ifelse(
    1 = rank ([{Customer Name} DESC ], ['']),
    'Yes',
    'No'
)
  1. Last Value to show the last string value
lastValue({Customer Name},[{Customer Name} ASC])

Hi @robdhondt

Thank you but this cannot work for me, I would like to have the output of “lastvalue” function without inserting in the visual, the field used in the function. The lastValue function, requires the original field to be inserted in the visual.

To achieve this, I transformed the text field in numeric, then calculate the max, then reconvert it into the previous format. This isn’t efficient at all, above all with millions of rows…

Thanks

@Andrea - Another approach. You can change the calculation level argument to PRE_AGG for the Rank function. Then you can use the calculated field in your other calculated fields without having the dimension in your field wells.

You can change the partition field in the rank according to your use case. For this example I’m showing the last alphabetical customer’s total sales by region.

c_last_alphabetical

ifelse(1=rank
(
  [{Customer Name} DESC ] 
  ,[Region]
  ,PRE_AGG
),'Yes','No')

c_last_customer_name

ifelse({c_last_alphabetical}='Yes',{Customer Name},NULL)

Hi @robdhondt , I appreciated the workaround but this won’t work for my case since the “Yes” and “No” classification will split the data (as you see in your sales column). I just want the max of a string to use it as a static value, so put into a calculated field, in a if-else condition.
Thanks

@Andrea – That’s just how I used it in the example. You don’t need to use it that way. Not knowing what your use case is, here is an example to return only the last alphanumeric customer’s sales.

c_last_alphabetical

ifelse(1=rank
(
  [{Customer Name} DESC ] 
  ,['']
  ,PRE_AGG
),'Yes','No')

c_last_customer_name

ifelse({c_last_alphabetical}='Yes',{Customer Name},NULL)

c_only_last_customer_sales

ifelse({c_last_alphabetical}='Yes',Sales,0)

c_only_last_customer_sales_by_name

sumIf(Sales,trim({c_last_customer_name})=trim('Zuschuss Donatelli'))

Hi @robhondt, I would like this but without that NULL. So having the last_alphabetical customer_name repeated for all the fields (in case calculated in the dataset) or aggregated to be a constant (in case calculated in the analysis).

I didn’t try, I will let you know once done but first impression is that it won’t work.

Thanks

@Andrea - Yeah this is a stubborn one. One last way you could achieve this, but it requires creating a calculated field for each value in your viz. You’d have to see how that stacks up against your costly transformation workaround.

  1. Create Over calculated fields for each value.
    image

  2. Exclude Filter the null c_last_customer_name

  3. You now have your desired output (I think)