Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I All,
I'm trying to write an expression with using if statement in order to sum multiple field values.
I've got "Source" field with 2 values: Order, Invoice.
I tried the following if statement:
if(Source='Order' and Source='Invoice', sum(OrderUnits) + sum(InvoiceUnits),
if(Source='Order', sum(OrderUnits), if(Source='Invoice', sum(InvoiceUnits))))
but I get wrong numbers.
I know that for multiple Or values I can use match(), in what function or condition can I use for multiple And values?
Thanks in advance,
Aviad
Or this
If(SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Order|') = 1 and SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Invoice|') = 1, Sum(OrderUnits) + Sum(InvoiceUnits),
If(Source='Order', Sum(OrderUnits),
If(Source = 'Invoice', Sum(InvoiceUnits))))
Try this:
If(GetSelectedCount(Source) = 2, Sum(OrderUnits) + Sum(InvoiceUnits),
If(Source='Order', Sum(OrderUnits),
If(Source = 'Invoice', Sum(InvoiceUnits))))
Or this
If(SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Order|') = 1 and SubStringCount('|' & GetFieldSelections(Source, '|,|') & '|', '|Invoice|') = 1, Sum(OrderUnits) + Sum(InvoiceUnits),
If(Source='Order', Sum(OrderUnits),
If(Source = 'Invoice', Sum(InvoiceUnits))))
Hi Sunny,
Both solutions work like a charm.
However, I prefer the second solution because it's generic and suitable for cases like field with three or more values by make it possible to combine different sets of values.
Thanks for your help!