Exclude negative values in Total aggregate value

[#1]

I have a Pivot Table where I am displaying how the Commodities perform over a date range, see Table below.

My problem is calculating the Total Leakage value, which should exclude all negative values in the row.

The calculations

1. Leakage calculation in its simplest form = (Lower GM%) - (Calculated GM%) * Sales

2. Where Lower GM% is selected by users from a table and has a value 0 for this example

3. Calculated GM% = (Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT)

and is shown in the table below

4. I display only the positive leakage values in the table and force the negative values to 0. I use this formula in QV to achieve this:

=Rangemax(0, ((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100 )

My Problem

5. My problem is when I am calculating the TOTAL LEAKAGE. I cannot exclude those negative values from my total leakage value. I have tried it using the following formula:

=Num((Aggr

(if(

(((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100) >= 0,

((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100,

0),

ESIS_Commodity))/1000,

'#####0','.',',')

Total Leakage '000	Commodity	Date	2010-10	2010-09
	5790 NEOPLAST TAPES AND DRESSINGS	Sales	20,429	1,037
	5790 NEOPLAST TAPES AND DRESSINGS	GM%	31.21%	-91,018.44%
937	5790 NEOPLAST TAPES AND DRESSINGS	Leakage	0	943679.16

For example:

In 2010/10, Leakage ~ -6375.89 (it's negative, so I display 0)

In 2010/9, Leakage ~ 943679

So Total Leakage should only count +ve values and display ~ 943,000

Instead it displays as ~937,000 = (943679-6375.89)

Would you have any suggestions on how I can exclude the negatives and what I am doing wrong? The business to getting impatient to release this tool now and I do not even know if it possible to even do this in QV9?

Many thanks in advance

Sahar

[#2]

Hello Sahar,

one idea is to use an addional field as flag. This field contains a '1' for positve values and a '0' for the negativ values. You can easily achieve this in your load script with an if-Statement like

load .... if (Amount< 0, 0,1) As AmountFlag.

After reloading you are able to use the flag in sum() like this

=Sum(AMOUNT * AmountFlag)

and you will sum up automatically only the positive values of AMOUNT.

HtH

Roland

[#3]

Hi Roland

Thanks for the quick reply. It did cross my mind to load the calculation in my SQL. However, 'Amount' is a dynamic value. It changes depending on what value, users have picked as their Lower value.

The formula behind it is:

Amount = (Lower {This value is picked by users from a table and have values 0 to 90} - CalculatedGM%) * SalesAmount

How will I manipulate the Amount value in my Load script?

[#4]

Sorry Sahar,

I did'nt read your post carefully. In a load script you can't guess any users choice. So Set Analysis has to do the work. Can you send a little sample application that makes it much easier to examine?

Regards, Roland

[#5]

HI Roland

Managed to get it to work finally with a few nested loops around the original formula:

Many thanks for your help thus far, Sahar

=Aggr( SUM(Num((Aggr

( if(

(((AVG(Lower) - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100) >= 0,

((AVG(Lower) - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100,

0),

ESIS_Commodity,Date)),

'#####0','.',',')),ESIS_Commodity)