How to fix a value.

[#1]

Hi guys,

I have a table

year ,product name ,amont , profit...

2011,a                    ,200,50

2012,b                    ,300,40

2011,a                    ,200,50

2012,a                    ,200,50

2011,c                        ,400,100

Now i want to calculate profit percentage on product name so

it would be (sum(profit) /sum( amount))*100

now query is amount for particular year should be constant everytime  but as if i select year 2011 , amount will be 800

but if i again select for product name 'a' then amount wont be constant , it will show 400

i want amount to be constant ....

plz help ASAP

Thanks,

Sachin.

[swuehl]

You can use set analysis to do this:

sum({1} amount)

will return the amount ignoring all selections.

sum({<[product name]=>} amount)

will return the amount ignoring only selections in product name.

[nilesh_gangurde]

Hi,

In this case you need to use the aggr() function.

(Aggr(sum(profit),year,product name) /Aggr(sum( amount),year,product name))*100

Hope this will be helpful.

-Nilesh

[MK_QSL]

Your question looks bit complicated.

Could you please describe little bit more.

Do you want Profit% for each product regardless of year... or

Do you want Profit% for each product for the respective years?

EDIT : Check enclosed file...

[jagan]

Hi,

Please find attached file for solution..

Regards,

Jagan.