Show Maximum Date per Code in Qlik Pivot Table

[pgalvezt2021]

Hello, I want the Qlik pivot table to show the maximum date per code from the total list of codes.

I'm currently using the following formula:

AVG({<[EG]={'CLOSED'}, [FEC CLI]={"$(=max([FEC CLI]))"}>} RT)

But this only gives me the overall maximum date across all codes. What I actually need is to display the maximum date for each code, since some codes have more than one date.

I attached an Excel file for better understanding.
I hope you can help me. Thanks!!!!

[marcus_sommer]

You may try something like:

avg(aggr(if(Date = max(Date), Value), Code))

[pgalvezt2021]

Thank you for your response Marcus.

The solution you proposed is not working completely.
When a code has only one associated date, the value is displayed correctly.
However, when a code has multiple dates, Qlik is not showing the maximum date as expected, and the result appears as null.
I really appreciate your help and any additional suggestions you can provide.

Thanks !!!!

[marcus_sommer · Accepted solution]

I think it could be done a bit simpler by avoiding an aggr() and outsourcing the condition. Here a quick simplified example:

[pgalvezt2021]

By following your solution step by step and applying it to my fields and data, I was able to get to the expected result. However, the issue lies in my final formula.

Let me explain: in the script, to calculate the final result, I apply a formula which — in the case of your example — would correspond to the value "V". In my case, this "V" is the difference between two dates.

When those two dates are equal, the result is 0, which is correct.
However, when I apply my formula, the AVG() function does not consider the 0s and instead treats them as null, which affects the final total.

My final formula is:
If(
AVG({<[ESTADO GRUPAL]={'CERRADO'}, Valida_Fecha={'1'}>} LT_1) *
-(Only([FEC ARRIB CLIENTE]) = max(Total <[N° PEDIDO MADRE]> [FEC ARRIB CLIENTE])) > 0,
AVG({<[ESTADO GRUPAL]={'CERRADO'}, Valida_Fecha={'1'}>} LT_1) *
-(Only([FEC ARRIB CLIENTE]) = max(Total <[N° PEDIDO MADRE]> [FEC ARRIB CLIENTE]))
)
And this is where the issue I mentioned earlier occurs.

Do you have any suggestions?

Thanks a lot!!!

[marcus_sommer]

An alternatively might be to include an extra information within the evaluation, basing for example of something like:

sign(Date1 - Date2) as OffsetDirection

Another approach may be not to use an avg() as logic else sum() / count() which should reflect the ZERO.

[pgalvezt2021]

Hello Marcus,

I’m going to mark the first part as the solution because that’s what actually solved my problem—the rest were just adjustments to the solution. Thank you so much for your help!!!!