topic How to fix a value. in QlikView

How to fix a value.

Sat, 23 Nov 2013 12:01:48 GMT

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 — Sat, 23 Nov 2013 12:28:45 GMT

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 — Sat, 23 Nov 2013 13:37:42 GMT

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 — Sat, 23 Nov 2013 15:41:44 GMT

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 — Sat, 23 Nov 2013 16:50:06 GMT

Hi,

Please find attached file for solution..

Regards,

Jagan.