IF statement in an expression

[#1]

I am trying to wirte an IF statement in an expression to either Add or subtract sales and costs depeneding on whether the sales figure is a '+' or a '-'. my code so far is

Sum(Aggr(If([Sales]='+',  Sum([Sales Amount (Actual)])-([InvoiceLine_UnitCost]*[Invoiced Quantity]) ,

         If([Sales]='-', Sum([Sales Amount (Actual)])+([InvoiceLine_UnitCost]*[Invoiced Quantity])))))

The [Sales] tag is an existing column in the chart.

[bbi_mba_76]

Hi,

maybe something like:

Sum(Aggr(Sum([Sales Amount (Actual)]+

          ( If([Sales]='+',-1,1) * [InvoiceLine_UnitCost]*[Invoiced Quantity]))))

[#3]

Hi,

This doesn't seem to return a number in my table. I get a - instead.?

[danieloberbilli]

or is just the dimension missing in the aggr() function?

Here I inserted something what I called SalesID at the end:

sum(aggr(if(Sales='+', Sum([Sales Amount (Actual)])-([InvoiceLine_UnitCost]*[Invoiced Quantity]),

if(Sales='-', Sum([Sales Amount (Actual)])+([InvoiceLine_UnitCost]*[Invoiced Quantity]))),SalesID))

[#5]

Can you please post the sample application

[#6]

Hi,

That returns a value of £0.0 now, so getting closer. In my table i have a sale of -£25.92 and cost of +£16.80. As the sale is a credit and negative in value, but the cost is a positive, i should return a value of -£9.12.

[danieloberbilli]

what about inserting a *(-1) for the part that should become negative (as these are costs):

sum(aggr(if(Sales='+', Sum([Sales Amount (Actual)])-([InvoiceLine_UnitCost]*[Invoiced Quantity]),

if(Sales='-', Sum([Sales Amount (Actual)])*(-1)+([InvoiceLine_UnitCost]*[Invoiced Quantity]))),SalesID))

[preminqlik]

hi try this

sum(If([Sales]='+', [Sales Amount (Actual)]-([InvoiceLine_UnitCost]*[Invoiced Quantity]),

         If([Sales]='-', [Sales Amount (Actual)]+([InvoiceLine_UnitCost]*[Invoiced Quantity]))

--->or

sum(aggr(sum(If([Sales]='+', [Sales Amount (Actual)]-([InvoiceLine_UnitCost]*[Invoiced Quantity]),

         If([Sales]='-', [Sales Amount (Actual)]+([InvoiceLine_UnitCost]*[Invoiced Quantity])),SalesID))

[#9]

Hi All,

Answers all seem to return a value of zero instead of correct number.

[rajeshvaswani77]

Hi Neil,

You will need to break it up in to parts and check teh values, or use a one or many list boxes with expressions.

thanks,

Rajesh Vaswani