only max date

[Simon4]

Hi all,

I have this table:

I only want to show the max startdate, in this case 11/3/2022 and the corresponding timetable_id. how do i achieve this? f.y.i. I have multiple employee_code

[KGalloway · Accepted solution]

For clarification, you want a table like this:

Employee_code, max(STARTDATE), timetable_id (of the max start date)

9147, 11/3/2022, 25

1234, 6/7/2022, 4

Is that correct?

If so, then a table with the following data should work:

Dimensions: employee_code

Measures: max(startdate) and firstsortedvalue(timetable_id, -startdate)

[Simon4]

Hi,

Thank you for your response.

Yes this is what I want to achieve. I already tried this but it does not work for me.

Why would this be?

[Renatusfreitas]

Olá @Simon4 , faça um teste trabalhando no script, aqui está uma forma de retornar apenas o último registro de uma lista agrupando pelo Employee_code.

Desta forma irá retornar uma tabela com a última Startdate e calendar_id para cada Employee_code.

Faça esse teste, Espero que essa solução contribua.

[Simon4]

Hi Renatusfreitas,

Thank you for your response.

This indeed works and has been my temporary solution.

but I need the timetable_id to be flexible because I have KPI's set up till 12 months back. now when someone switches timetable_id the KPI's will result faulty data, because the hours connected to the timetable_id changed.

So I imagine it can't be done in the data load editor because then the timetable-id and startdate are fixed.

any other suggestion?

[WildmoserGeorg]

Hi,

KGalloway solution works very well. Is it possible that you have the formula put in dimesion instead of expression?

BG

[WildmoserGeorg]

or you have a problem with the format. then try:

firstsortedvalue(Calendar_id, -date(Startdate))

[Simon4 · Accepted solution]

Hello Galloway,

You where right after all. But I also needed distinct.

firstsortedvalue( timetable_id, -aggr(max(if(STARTDATE<(vPeildatum)+1,STARTDATE)), Employee_code))

 thanks for helping.