Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.



Tags (3)
12 Replies
bbi_mba_76
Valued Contributor

Re: IF statement in an expression

Hi,

maybe something like:

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

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

Not applicable

Re: IF statement in an expression

Hi,

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

danieloberbilli
Valued Contributor II

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

Not applicable

Re: IF statement in an expression

Can you please post the sample application

Not applicable

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.

danieloberbilli
Valued Contributor II

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

preminqlik
Valued Contributor II

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


Not applicable

Re: IF statement in an expression

Hi All,

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

rajeshvaswani77
Valued Contributor III

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

Community Browser