What is so peculiar with the number 10?

[Gerard_Chevalie]

Hi,

In a table, I have this formula for a virtual column :

SUM(SELECT(E_Entrées_Coll[Nombre], AND([Nom/couleur]=[_THISROW].[Nom/couleur],[Dimensions]="15x15"),TRUE)

This is what happens: in the column [Nombre] of the table E_Entrées_Coll, when the value of a cell is 10, the value seems to be ignored and is not integrated in the SUM result. When the value is 1 or 9 or 11 or 20, the sum is correctly done.

Any idea of the cause of this strange phenomenon?

Thanks in advance for your help

Gérard

[Swoopy]

SUM(
   SELECT(
      E_Entrées_Coll[Nombre],
      AND(
         [Nom/couleur]=[_THISROW].[Nom/couleur],
         [Dimensions]="15x15"
      ),
      TRUE
   )
)

[Gerard_Chevalie]

Thanks but...

the last ) was omitted in my original message but it is definitely present in the app formula, so this is not the cause

[Swoopy]

Can you show table E_Entrées_Coll including the row with value of 10 ?

[Gerard_Chevalie]

Here is a screen copy of the table, last rows

[Swoopy]

Can you also show column [Nom/couleur] ?

[Gerard_Chevalie]

It's a virtual column 

CONCATENATE([Nom],"/",[Couleur])

[WillowMobileSys]

It would be best if you can show an example of where the result is incorrect.  It may be you are mis-reading what the expected result should  be.

From what I am seeing, this is only a single row that can match the criteria where column [Nombre] = 10, so the expected result would be 10.  Are you not getting that as the result?

If not, then make sure the values of this part "...[Nom/couleur]=[_THISROW].[Nom/couleur]..."  are comparing like values. 

NOTE: There are two [Nom/couleur] columns - the one in the table and the one you are comparing against referenced by [_THISROW].  How are you setting [_THISROW].[Nom/couleur]?

[Gerard_Chevalie]

Here are screenshots to show you what happens when I only change the value of the column [Nombre] in the table:

When it is set to 11 :

the SUM result in my app is 46 :

The AppSheet test of the formula shows its calculation :

When the value is set to 10 (really sole modification in the tables and the app) : 

the SUM result in the app shows 35 although it should be 45 :

The AppSheet test of the formula shows the calculation omitted the last value 10 :

Still mind boggling to me...

[Swoopy]

OIZ, try

SUM(
   SELECT(
      E_Entrées_Coll[Nombre],
      AND(
         [Nom/couleur]=[_THISROW].[Nom/couleur],
         [Dimensions]="15x15"
      )
   )
)

[Gerard_Chevalie · Accepted solution]

OK, got it !

With the TRUE parameter, the app considered 10 as a duplicate to be ignored...

Thanks a lot for having opened my eyes on this !

Gérard