topic Re: How to retrieve max date based on the last value of another field in App Development

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

juanjaramillo1 — Wed, 06 Jul 2022 07:56:32 GMT

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!

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

Or — Wed, 06 Jul 2022 09:34:18 GMT

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.

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

juanjaramillo1 — Wed, 06 Jul 2022 12:02:29 GMT

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!

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

Or — Wed, 06 Jul 2022 12:12:59 GMT

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.

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

juanjaramillo1 — Wed, 06 Jul 2022 12:51:27 GMT

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 🙂

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

Or — Wed, 06 Jul 2022 12:54:29 GMT

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).

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

juanjaramillo1 — Wed, 06 Jul 2022 12:55:55 GMT

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

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

Or — Wed, 06 Jul 2022 13:00:06 GMT

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