Solved: Help with max formula - Page 2

Sergio_Sa · 11-09-2019 10:01 AM

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

Steve

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:

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

View solution in original post