How to retrieve max date based on the last value of another field

[juanjaramillo1]

Hello community. I have searched in the portal but did not found any answer. I hope you can help me with this!

I have a table with dates in ascending order and want to retrieve the maximal (aka. last) date of the last value of another field. This other field is named "Umsatz_Ist_Netto_EUR".

In my uploaded example, I have to be able to retrieve 2022-06-09, which is the maximal date based on the last value of the field "Umsatz_Ist_Netto_EUR". Trying with FirstSortedValue does not work for me, or I am using it wrong... 

Would love I you can help me with this please.

Thank you very much in advance!

[Or]

So, what you're looking for is the last date that has a EUR value associated with it?

Date(Max({< Umsatz_Ist_Netto_EUR = {"*"}  >} Datum))

Note that if the date is actually a string rather than a proper date, you'll  need to use MaxString() and Date#() instead.

[juanjaramillo1]

Thank you for the answer! Sadly I receive 2022-12-31, which is the last date of the table. What I want to achieve is to retrieve the last date that has values of the field "Umsatz_Ist_Netto_EUR" associated with it, in this case, the date output has to be 2022-06-09, as in the example. Maybe the output gives  2022-12-31 because the date field is associated with the "...EUR" field even when in that field I do not have any value, therefore it is associated till the end of the date field ? 

One pic shows my result. 

Second pic shows the end of the table. Maybe that helps to help me. Thank you very much!

[Or]

If that EUR field is blank (or whitespace) rather than null, this won't work. I can't tell whether this is the case from your image. I can confirm that the logic does work when the missing values are null, since I tested it locally before posting. If your values are actually a blank, you could try:

{< Field2 -= {""} >} instead of the ={"*"} version. {< Field2 = {"?*"} >} Should also work for this use case, I think.

[juanjaramillo1 · Accepted solution]

Hi Or,

that worked! I was trying to accomplish that for weeks! 

This worked out well:  Date(Max({< Umsatz_Ist_Netto_EUR = {"?*"} >} Datum)) 

Thank you very much 🙂 

[Or]

Sure thing. That that says is that the Umsatz_Ist_Netto_EUR must contain any value that is at least one character long ('?' designates a single character).

[juanjaramillo1]

Nice, thank you for the tip! And what says (*) ?

[Or]

Same as in search, any value of any length. Here's a resource that details these options and more:

https://community.qlik.com/t5/Qlik-Design-Blog/Qlik-Sense-Cheat-Sheet-version-2-0/ba-p/1667455