Hi all,
Iโve come slightly stuck on an expression for an app iโm building and canโt get my head around it.
I have 2 tables โIn Stockโ and โExpensesโ
โIn Stockโ table has a VC that sums the expenses related to each individual stock item.
In the โExpensesโ table I have a column called โVAT Applicableโ the data type is Enum with options of โYesโ and โNoโ
Essentially what iโm trying to achieve is:
SELECT Expenses[Price] WHERE [Car Id] = [_THISROW].[ID] AND Expenses[VAT Applicable] = Yes
The expression formula iโve currently got is:
SUM(
SELECT
(
Expenses[Price],
AND(
([Car Id] = [_THISROW].[ID]),
(IN( Yes , Expenses[VAT Applicable] ) = Yes)
)
)
)
This expression just returns all the expenses related to that ID regardless of [VAT Applicable] being โYesโ or โNoโ
Thanks in advance,
Look forward to your responses,
Dave.
Hi @David_Taylor,
Can you try:
SUM(
SELECT(Expenses[Price],
AND(
[Car Id] = [_THISROW].[ID],
[VAT Applicable]
)
)
)
I assume here that [VAT Applicable] is of type Yes/No.
If not, please try:
SUM(
SELECT(Expenses[Price],
AND(
[Car Id] = [_THISROW].[ID],
[VAT Applicable]="Yes"
)
)
)
Let us know if that works
Cheers
Tried the second option:
SUM(
SELECT(Expenses[Price],
AND(
[Car Id] = [_THISROW].[ID],
[VAT Applicable]="Yes"
)
)
)
All working thank you very much for your help!
Think half my issue in my experimenting was having the table name in front of the column when I didnโt need it. โExpenses[VAT Applicable]โ
Thanks this helped alot!
Probably, SELECT expressions may be tricky sometimes.
You are welcome !
User | Count |
---|---|
37 | |
30 | |
29 | |
20 | |
18 |