Help with max formula

[Sergio_Sa]

Hello everyone,
I have 2 tables
HOME is Parent (key= [CPR])
INFOPREX is child
I want to get the maximum value in each ‘LOCALIZACAO’.
My formula is (there are more conditions but let’s go to the main one):

IFS(USERSETTINGS('Numero de Meses Análise')=6,
MAX(
LIST(ANY(SELECT([Related INFOPREXs][V_0],[LOCALIZACAO]='Loja 1')),
ANY(SELECT([Related INFOPREXs][V_1],[LOCALIZACAO]='Loja 1')),
ANY(SELECT([Related INFOPREXs][V_2],[LOCALIZACAO]='Loja 1')),
ANY(SELECT([Related INFOPREXs][V_3],[LOCALIZACAO]='Loja 1')),
ANY(SELECT([Related INFOPREXs][V_4],[LOCALIZACAO]='Loja 1')),
ANY(SELECT([Related INFOPREXs][V_5],[LOCALIZACAO]='Loja 1'))
)
)
)

The problem is that the values displayed after each synchronization are different.
I’d appreciate some help figuring out what I’m doing wrong.
Thanks

[Bellave_Jayaram]

You cannot filter Related INFOPREX like that. Replace

Sergio_Sa:

[Related INFOPREXs]

by INFOPREX (the name of the child table)

[Sergio_Sa]

Thanks @Bellave_Jayaram for the reply but with this change always returns 0.

In your suggestion the expression does not take into account the key of the row for which I want the expression. I probably need an ‘AND’ statement, or am i wrong?
I still have some difficulties turning Portuguese into AppShett’ese.
I will keep trying.
EDIT: In the image there are only zeros, but in other rows there are values, which I want to find the maximum value for each Loja(1,2 or 3) of the INFOPREX table, in each row of the HOME table.

[Steve]

Your use of [Related INFOPREXs] (e.g., [Related INFOPREXs][V1]) is valid.

[Steve · Accepted solution]

I cannot explain why you’re seeing different numbers each time the app syncs. That is very odd behavior.

My interpretation of the expression sample you provided is that you are attempting to find the highest V_ column value–where each V_ column corresponds to a 1-month period–for the Loja 1 location over a number of months determined by the Numero de Meses Análise user setting (6 in the sample). Based on my interpretation, I propose an alternative approach:

1. Add a virtual column (I’ll call it MAX_V) to the INFOPREX table to compute the maximum of the first so-many V_ column values–as determined by the Numero de Meses Análise user setting–for its row:

   MAX(
     TOP(
       LIST([V_0], [V_1], ..., [V_15]),
       USERSETTINGS('Numero de Meses Análise')
     )
   )
   

   Replace [V_0], [V_1], ..., [V_15] with the complete list of V_ column references for the row.

   TOP(..., USERSETTINGS('Numero de Meses Análise')) reduces the list of all V_ column values to only the first so-many determined by the user setting.

   MAX(...) then finds the highest of the first so-many of row’s V_ column values.

2. Replace your IFS(USERSETTINGS(...)=6, MAX(SELECT(...))) expression with the following:

   MAX(SELECT([Related INFOPREXs][MAX_V], ('Loja 1' = [LOCALIZACAO]))
   

   Note how I’ve expressed ('Loja 1' = [LOCALIZACAO]). The is-equal-to operator (=) has the non-intuitive behavior that it will evaluate as TRUE if the left-side value is blank, regardless of the right-side value. If [LOCALIZACAO] on the left side is ever blank, you might get unexpected results. Putting a value that is guaranteed to be non-blank on the left side avoids this behavior.

With these expressions, you won’t need separate expressions to handle each different value of the Numero de Meses Análise user setting.

If you aren’t interested in trying this alternative, we can instead try to troubleshoot the odd behavior your app is experiencing.

[Sergio_Sa]

Thank you very much @Steve you are the Man.
I was trying to avoid putting VC’s in the INFOPREX table because it’s a table with 52300 rows x 31 columns. I thought it would have a lot of impact on synchronization, but it didn’t.
I learned a lot from your answer. Thank you very much.

[Sergio_Sa]

Good Morning @Steve.

The behavior I mentioned yesterday about the formulas returning different values ​​on each sync reappeared after I added some more VC’s based on your solution.

What seems to me to happen is that AppSheet does not calculate all rows. When I go into detail view and activate an action that is not related to calculations but sets a value in the table, AppSheet does the calculation.
Is there any way to force AppSheet to do all calculations before displaying views?

[Sergio_Sa]

After several attempts and experiments, I realized that replacing:

Steve:

MAX(SELECT([Related INFOPREXs][MAX_V], (‘Loja 1’ = [LOCALIZACAO]))

(That for me was the best solution), with:

MAX(SELECT(INFOPREX[MAX_V],AND( [CPR]=[_THISROW].[CPR],('Loja 1' = [LOCALIZACAO]))))

as well as all other VC’s where I used [Related INFOPREXs], the app works well.

So there seems to be a sync issue when we use [Related TABLEs] with a lot of data. I do not know if that is, but my change solved the problem.
Thank’s by all help.