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]
Try removing the first instance of "[_THISROW]." (before the "[mom_or_kid]")
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")
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]
)
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)
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.
index() will dont do what i want to get
@JPAlpano wrote:I just had this issue today and it seems LOOKUP is not behaving property in a VC.
Huh? Details?
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
User | Count |
---|---|
43 | |
27 | |
23 | |
14 | |
12 |