topic Re: in % relation column table top 5 in QlikView

in % relation column table top 5

Mon, 20 Jun 2016 16:34:22 GMT

Hi guys,

so I have a table comparing my top 5 salesmen in units. My Dimension is name_salesperson and the expression for my measure looks like this:

Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE)

This works fine.

Now I would like to add a column measure showing the ratio of their sold units relative to all sold (chair) units. My expression looks like this:

(Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE))

(Sum({$<value_type={"units"}, name_salesperson={"*"}, product_type={"chairs"}>}VALUE))

Unfortunately this comlumn gives me a 1 (or 100%) for each salesman. The inserted pic is a example of what I would like to see in the end. (Im using the rank function so i can also display other salesmen, that I'd like to compare to my top 5 by the way.)

Any help is appreciated!

Re: in % relation column table top 5

johnw — Mon, 20 Jun 2016 20:00:53 GMT

You may just need "total" before the last "VALUE".

...chairs"}>} total VALUE))

Since name_salesperson is a dimension in your chart, your expression is still evaluated in that context, and so your /sum() is always for a single salesperson. Using "total" tells that sum to ignore the chart dimension(s).

Re: in % relation column table top 5

sunny_talwar — Mon, 20 Jun 2016 22:06:30 GMT

In addition do you have null in Salesperson field which you are trying to ignore from the total on the bottom? If not, then you can might not really need this (name_salesperson={"*"}) in the denominator?

Try this:

(Sum({$<value_type={"units"}, name_salesperson={"=rank(sum(VALUE)>5)"}, product_type={"chairs"}>}VALUE))

(Sum({$<value_type={"units"},product_type={"chairs"}>} TOTAL VALUE))

John's suggest is in Green above.

HTH

Best,

Sunny