Return distinct value where max date and How to convert date time string in Qlik Sense?

[appelture]

Hi, from the example data below I need to return the Model Name at the max Modified Date (which is a date time string - so this needs converting).

Model Name	Modified Date (Date Time String)
Model A	2000-10-12 08:45:27
Model C	2008-10-30 16:16:19
Model A	2009-11-07 13:17:28
Model B	2012-06-05 10:17:11
Model A	2002-01-12 14:17:05
Model C	2003-02-20 14:23:00
Model C	2000-01-25 13:18:29

I am very new to Qlik but have seen the following formula that I think might work, however, I don't know how to also add in the conversion of the date time string: 

Sum(Aggr(
    If(Date = Max(TOTAL <ModelName> Date)), ModelName, Date))

Any help would be greatly appreciated. Thanks

[Andrei_Cusnir · Accepted solution]

Hello,

If my understanding is correct, then you want to display the value "Model B", because the biggest date from the dataset is "2012-06-05 10:17:11". Here are the steps that I have followed and some clarifications on your questions:

1. I have the following dataset:

2. Now I have created an Text & image object to display the value that you want

3. If you use as measure:  =Max([Modified Date (Date Time String)]) then the result is:

4. If you want this value to be formatted as date, then you can use the expression =Date(Max([Modified Date (Date Time String)]), 'DD/MM/YYYY hh:mm:ss') which will give you the output:

5. If you want to display the Model Name of the latest date though, you can use the expression: =FirstSortedValue([Model Name], -[Modified Date (Date Time String)]) which will give you the output:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[abhijitnalekar · Accepted solution]

Hi @appelture ,

you can follow the below steps to get the required output

1. COnver date time filed to Date

date(Modified_Date,'YYYY-MM-DD') as Modified_Date 

2. Use the below expression to get the Model names in front of max date

=if(Modified_Date = Max(TOTAL <ModelName> Modified_Date),ModelName)

I hope i have understood the requirement correctly.

[salonicdk28 · Accepted solution]

Hi,

is this you need as an output-

Below steps to follow in script editor to achieve the above output

Temp:
Load * Inline [
ModelName,ModifiedDate
Model A,2000-10-12 08:45:27
Model C,2008-10-30 16:16:19
Model A,2009-11-07 13:17:28
Model B,2012-06-05 10:17:11
Model A,2002-01-12 14:17:05
Model C,2003-02-20 14:23:00
Model C,2000-01-25 13:18:29
];

Load ModelName,
Date(max(ModifiedDate)) as Date,
FirstSortedValue(ModelName,ModifiedDate)
Resident Temp
Group by ModelName;

Drop Table Temp;