Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

in % relation column table top 5

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!

exampleofinpercent.png

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: in % relation column table top 5

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).

2 Replies
MVP
MVP

Re: in % relation column table top 5

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

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

Community Browser