topic Re: IF statement in an expression in QlikView

IF statement in an expression

Wed, 08 Jan 2014 11:22:21 GMT

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

bbi_mba_76 — Wed, 08 Jan 2014 11:26:12 GMT

Hi,

maybe something like:

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

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

Re: IF statement in an expression

Wed, 08 Jan 2014 11:49:25 GMT

Hi,

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

Re: IF statement in an expression

danieloberbilli — Wed, 08 Jan 2014 11:51:29 GMT

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

Wed, 08 Jan 2014 11:51:52 GMT

Can you please post the sample application

Re: IF statement in an expression

Wed, 08 Jan 2014 12:05:07 GMT

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

danieloberbilli — Wed, 08 Jan 2014 12:14:38 GMT

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

preminqlik — Wed, 08 Jan 2014 12:26:02 GMT

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

Wed, 08 Jan 2014 14:16:18 GMT

Hi All,

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

Re: IF statement in an expression

rajeshvaswani77 — Wed, 08 Jan 2014 14:19:28 GMT

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

danieloberbilli — Wed, 08 Jan 2014 14:35:46 GMT

Please attach an example qvw file to have a closer look

Re: IF statement in an expression

christian77 — Wed, 08 Jan 2014 14:37:31 GMT

Hi.

Use set analysis.

Re: IF statement in an expression

Wed, 08 Jan 2014 14:54:02 GMT

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