help with expression

[hopkinsc]

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?

[#2]

Hi

You could do something like:

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

[hopkinsc]

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?

[#4]

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

[#5]

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

[hopkinsc]

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.

[#7]

Plz share sample data with expression in QV.

[hopkinsc]

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!

[hopkinsc]

Any ideas anyone?

[nathanfurby]

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