virtual column result not correct

[ABO3ADL]

hello great members

i have issue with one of virtual column that's catch not updated value (the value that before the last one)

the expression : result 1010.63 

SWITCH(
[_THISROW].[mom_or_kid],
        "kid",
              LOOKUP(
            [_THISROW].[Serial],"kid","Serial","total_cost"
                       ),
         "mom",
              LOOKUP(
                  [_THISROW].[Serial],"mom","Serial","total_cost"
                       )
          ,"")

the expression of [total_cost] in table "mom" result 850 

[price]+[feed cost]+[other cost]+[vac cost]

[Marc_Dillon]

Try removing the first instance of "[_THISROW]." (before the "[mom_or_kid]")

[ABO3ADL]

same result

also i dont think the problem is the expression 

i try this expression to see result of particular row and also give wrong result

LOOKUP("8a79c762","mom","Serial","total_cost")

[ABO3ADL]

table "mom"

[total_cost]:

[price]+[feed cost]+[other cost]+[vac cost]

[price] is sheet column and not change

[feed cost] is virtual column expression :

SUM(
     SELECT(
             feed date[cost head],
             IN(
                [_THISROW].[Serial],[Serial]
                )
            )
      )
   -
sum(
      [Related kids][birth feed cost]
    )

[other cost] is virtual column expression :

SUM(
     SELECT(
              expenses_detail[cost kid mom same date],
              and([expense_id].[ledger]="exp",
                 in(
                    [expense_id].[expenses type],LIST("exp2","exp1")
                    ),
                 IN(
                     [_THISROW].[Serial],[cost Serial]
                    )
                   )
               )
       )
-
sum(
     [Related kids][birth other cost]
    )

[vac cost] is virtual column expression :

SUM(
     SELECT(
            vaccines_table[cost],
              AND(
                   [done?]="y",[_THISROW].[Serial]=[kid_or_mom_serieal]
                  )
             )
    )
-
SUM(
  [Related kids][birth vac cost]
    )

[Swoopy]

To me, the default-result should be number (zero in this case)

SWITCH(
[_THISROW].[mom_or_kid],
        "kid",
              LOOKUP(
            [_THISROW].[Serial],"kid","Serial","total_cost"
                       ),
         "mom",
              LOOKUP(
                  [_THISROW].[Serial],"mom","Serial","total_cost"
                       )
          ,0)

[JPAlpano]

I just had this issue today and it seems LOOKUP is not behaving property in a VC.

I ended up using an INDEX( SELECT() , 1) and it worked as expected.

Didn't observe the issue on LOOKUPS for physical columns.

[ABO3ADL]

index() will dont do what i want to get

[Steve]

---

@JPAlpano wrote:

I just had this issue today and it seems LOOKUP is not behaving property in a VC.

---

Huh? Details?

[ABO3ADL]

i observe its give some times when add new row to any table thats effect on the looking up column 

in general its give correct result