12 Replies Latest reply: Jul 22, 2010 5:46 AM by Chris Hopkins

# help with expression

Hi, i have an application which displays promotion statistics. i have a field called DealNumbers which has several hundres dealnumbers.

I then have an expression which calculates the total sales and divides it by 3.

I only want to have this expression to populate when a specific deal is selected (900000)

Any ideas?

• ###### help with expression

Hi

You could do something like:

`=SUM(IF(GetFieldSelections(DealNumbers)='900000',Sales))/3`

• ###### help with expression

Hi,

Thanks for your reply, This doesn't seem to work for me though...

It works as far as selecting the correct deal number, i.e.

It displays a result when the deal number 900000 is selected, and displays - when the wrong deal number is selected, but it displays the wrong totals.

If i use

Sum (SalesQuantity) /3

then for the first result it displays '140'

If i also add in the expression

=

SUM(IF(GetFieldSelections([DealNumber])='900000',SalesQuantity))/3

so now both are side by side, it displays '1960'

Both are using the same fields and looking at the same data, but bringing back different information.

am i doing something wrong?

• ###### help with expression

You could use the expression

sum ( if (DealNumbers = 900000 , Sales , )) / 3

Or would be easier if you could upload some sample data to explain your problem

• ###### help with expression

Hi,

Plz see attachment.

Keep your table/expression as it is.

Create separat list box of DealNumbers.

Whenever you will click on any of the deal number, you will get expression for only that/those deal number/s.

Happy Thoughts

• ###### help with expression

Thanks but that won't work for me as not all deal numbers are to be divided by 3. some are divided by 5, some by 2 etc.

• ###### help with expression

Plz share sample data with expression in QV.

• ###### help with expression

Hi, here is my document,

Sorry its so big, Having trouble trying to replicate it.

The DEALS SALES TOTAL is the expression given on this forum (which is displaying the wrong info.

The =sum(salesQuantity)/3 is displaying the correct info, but for all deal numbers. where i only want a specific deal number to display info, the rest be a zero or '-'.

For the attached document, i am specifying deal number 4986, not 900000 as posted in my original post.

Help would be appreciated!

• ###### help with expression

Any ideas anyone?

• ###### help with expression

If it was me then I would create a mapping table with the necessary factors at load time and then perform calc on that. (See attached example)

You could also just create a calculated column (Deal Sales Total) on load time using a mapping table and then reference the column directly.

Hope it helps

• ###### help with expression

Thanks everyone for your replies, i managed to get it working by using:

If (DealNumber =900000 , sum (salesQuantity)) / 3

:)

• ###### help with expression

How does that expression work when the DealNumber does not equal 900000?

Also - what happens if other deal numbers need a factor of some kind, or what if the factor changes? Much better to manage this with data rather than in the expressions - imho

• ###### help with expression

If it does not equal 900000, it just displays -.

We are only using this application to report on a specific deal number (900000) the application can be used to look at other deals too, but no calculation will be needed.

Currently our API database does not dump out the table required to give us the result needed (but it will later on this year), so currently we would not require other deal numbers to have specific factors.

:)