SUM IF Statement

[Guest]

How do I design a query that will multiply the revenue by different values
when the sales stage is a certain value?

For example:

Sales Stage Revenue
01 $100
01 $150
02 $100
02 $150

I want to mulitply the Revenue by .10 when the Sales Stage is 01 and by .20
when the Sales Stage is 02.

Is there a way to design a single query to do this?

 

[Guest]

This will only work for the two rates that you show in your post; more than
two rates and this won't work directly (possibly a choose() clause rather
than an iif()):
In SQL: SELECT Stage, Rev, Rev + iif (Stage = 1, .10, .20) * Rev
from RevTbl;

The QEB Grid looks like:

Field: Stage Rev Expr1: Rev + iif (Stage = 1, .10, .20)
* Rev
Table: RevTbl RevTbl
Show: (Checks in each box)

This is for a simple table constructed from your sample data:
RevTbl (Stage numeric, Rev currency)

Alternative using choose() is:

select *, Rev + choose(Stage, .1, .2) * Rev from RevTbl;

The QEB Grid looks identical except that Expr1: Rev + Choose (Stage, 0.1,
0.2) * Rev

This second version is more extendable, since if you need to add more
Stages, (say 3, 4, and 5), you simply add the associated factor(s) into the
choose list ( as in choose(Stage, .1, .2, .3, .4, .5).

Good Luck!

 

[Guest]

If you only have two categories it is simple --
Iif([Stage]="01", [Revenue]*.1, [Revenue]*.2)

If you have a lot of different categoeries then you would need a another
approach.

Build a Lookup table like –
Stage Multiplier
01 .1
02 .2
03 .5
04 .7

FIELDS: Stage Revenue
Multiplied:[Revenue]* [Lookup].[Multiplier]
CRITERIA: [Lookup].[Stage]

 

[Wayne Morgan]

In the example you have

[Revenue] * ([Sales Stage] / 10)

will do it. However, if it is going to get more complicated, you can use
nested IIf statements.

IIf([Sales State]="01", [Revenue] * 0.1, IIf([Sales Stage]="02", [Revenue] *
0.2))

 

[Guest]

Karl,

This seems to be the best approach since I would be able to change the
multiplier at will w/out going into the query and changing the expression.

However, the query is prompting me to "Enter Parameter Value" for the
Multiplier.

I have the expression in the Revenue field [Revenue] * [Lookup].[Multiplier]
and in the Critieria (also in the Revenue field) I have [Lookup].[Stage]

What am I doing wrong?

If you only have two categories it is simple --
Iif([Stage]="01", [Revenue]*.1, [Revenue]*.2)

If you have a lot of different categoeries then you would need a another
approach.

Build a Lookup table like –
Stage Multiplier
01 .1
02 .2
03 .5
04 .7

FIELDS: Stage Revenue
Multiplied:[Revenue]* [Lookup].[Multiplier]
CRITERIA: [Lookup].[Stage]

How do I design a query that will multiply the revenue by different values
when the sales stage is a certain value?

For example:

Sales Stage Revenue
01 $100
01 $150
02 $100
02 $150

I want to mulitply the Revenue by .10 when the Sales Stage is 01 and by .20
when the Sales Stage is 02.

Is there a way to design a single query to do this?

 

[Guest]

Never mind my last post...I figured it out.

I took out the [Lookup].[Stage] in my query and it works perfectly. I'm
thinking the reason it is working now is because [Stage] is part of the query
already, so there is no need to look it up as long as the two fields from the
two tables are joined.

Thank you for the help!

If you only have two categories it is simple --
Iif([Stage]="01", [Revenue]*.1, [Revenue]*.2)

If you have a lot of different categoeries then you would need a another
approach.

Build a Lookup table like –
Stage Multiplier
01 .1
02 .2
03 .5
04 .7

FIELDS: Stage Revenue
Multiplied:[Revenue]* [Lookup].[Multiplier]
CRITERIA: [Lookup].[Stage]

How do I design a query that will multiply the revenue by different values
when the sales stage is a certain value?

For example:

Sales Stage Revenue
01 $100
01 $150
02 $100
02 $150

I want to mulitply the Revenue by .10 when the Sales Stage is 01 and by .20
when the Sales Stage is 02.

Is there a way to design a single query to do this?