Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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...
Hi,
Please find attached file for solution..
Regards,
Jagan.