
Exclude negative values in Total aggregate value
Roland Kunle Nov 29, 2010 8:12 AM (in response to a1qczzz)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 ifStatement like
After reloading you are able to use the flag in sum() like thisload .... if (Amount< 0, 0,1) As AmountFlag.
and you will sum up automatically only the positive values of AMOUNT.=Sum(AMOUNT * AmountFlag)
HtH
Roland

Exclude negative values in Total aggregate value
a1qczzz Nov 29, 2010 8:48 AM (in response to Roland Kunle )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?

Exclude negative values in Total aggregate value
Roland Kunle Nov 29, 2010 10:13 AM (in response to a1qczzz)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

Exclude negative values in Total aggregate value
a1qczzz Dec 1, 2010 12:15 PM (in response to Roland Kunle )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)


