12 Replies Latest reply: Jan 8, 2014 9:54 AM by Neil Perkins

# IF statement in an expression

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.

• ###### Re: IF statement in an expression

Hi,

maybe something like:

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

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

• ###### Re: IF statement in an expression

Hi,

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

• ###### Re: IF statement in an expression

Can you please post the sample application

• ###### Re: IF statement in an expression

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))

• ###### Re: IF statement in an expression

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.

• ###### Re: IF statement in an expression

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))

• ###### Re: IF statement in an expression

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))

• ###### Re: IF statement in an expression

Hi All,

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

• ###### Re: IF statement in an expression

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

• ###### Re: IF statement in an expression

Please attach an example qvw file to have  a closer look

• ###### Re: IF statement in an expression

ok. Just trying to upload. For some reason the ability to upload to the discussion forum is greyed out in my menu.

• ###### Re: IF statement in an expression

Hi.

Use set analysis.