topic AW:selected divided by not selected in QlikView

selected divided by not selected

Mon, 21 Feb 2011 18:04:16 GMT

Hi Qlikview community

how can I divide the value in a selection by the sum without selection?

See Screenshot: I choose Berlin and receive the value 1540 But I want this selected value divided by their sum total.

In this case like (1540) divided by (1200 +1500 +1540 +1750 +1850).

Thank you!

Best regards,

AW:selected divided by not selected

brenner_martina — Mon, 21 Feb 2011 18:07:16 GMT

Hi,

in Set Analysis you find P() for selected and E() for not selected!

AW:selected divided by not selected

Mon, 21 Feb 2011 18:26:33 GMT

Hi Martina,
how would my formula look like? Unfortunately, I dont understand the documentation

Can you please give me an example? Thank you very much.

Best regards,

AW:selected divided by not selected

Anonymous — Mon, 21 Feb 2011 18:35:23 GMT

It will be something like this:
sum(Revenue)/sum({$<Unit=E({$} Unit)>} Revenue)

selected divided by not selected

kaushalview — Mon, 21 Feb 2011 18:54:34 GMT

please See attached example

Regards

kaushal Mehta

selected divided by not selected

kaushalview — Mon, 21 Feb 2011 18:55:40 GMT

See attached example

Regards

Kaushal Mehta

AW:selected divided by not selected

Mon, 21 Feb 2011 19:32:48 GMT

Hello Michael,

unfortunately I cant use the shown example on my final formula.

= sum( aggr(sum([HEL_DATA_Imps mit Defaults]) * (SAL_OPPORTUNITYLINEITEM_UnitPrice), SAL_OPPORTUNITYLINEITEM_UnitPrice))

How should look my final formula and what i have to change ? I tried to transform your example in to mine like this:

= sum( {$<Unit=E({$} Unit)>} aggr(sum( [HEL_DATA_Imps mit Defaults] ) * (SAL_OPPORTUNITYLINEITEM_UnitPrice), SAL_OPPORTUNITYLINEITEM_UnitPrice))

But it didnt work ! What's wrong

Thank you very much

Best Regards,

AW:selected divided by not selected

rbecher — Mon, 21 Feb 2011 21:13:48 GMT

Hi Onur,

the problem is that Set Analysis will not work on your expression because it only works on functions like SUM. You have to rewrite your expression to avoid the multiplication outside the SUM function like this:

= 'Totalprice PLAN IST: ' & sum({1} aggr({1} sum({1} [HEL_DATA_Imps mit Defaults] * SAL_OPPORTUNITYLINEITEM_UnitPrice), SAL_OPPORTUNITYLINEITEM_UnitPrice))

This example will calculate the wrong figure in your case I guess. But, it shows how the Set Analysis would work.

- Ralf