topic Re: Hit rate calculation in QlikView

Hit rate calculation

Wed, 25 Nov 2020 16:16:04 GMT

Hello ,

I am new in the use of qlikview. So it is not easy for me to figure out a way there are lots of ideas coming in my mind and none of them working. I have some information in diferents stage gate. i would like to calculate a hit rate. here is the problem how can i select the data at stage won/lost then get the count in order to be able to perform the ratio. Where can i perform these kind of operations.

thanks for your help

Re: Hit rate calculation

krishnacbe — Wed, 11 Jan 2017 16:25:42 GMT

Hi,

If you want to show as a KPI, you can use either Textbox or Gauge charts.

even simple bar/Pie chart is fine.

Can you explain in detail or share the expression/ provide sample input and output you need to help you more.

Re: Hit rate calculation

Wed, 11 Jan 2017 16:49:21 GMT

Hello

Thanks for your answer:

As you can see in the screen below i would like to perform a formula on :

count (closed -won)/count (closed -won)+ count (closed - lost).

thanks for your support,

regards,

Re: Hit rate calculation

krishnacbe — Wed, 11 Jan 2017 17:01:17 GMT

Hope below expression helps

count({<Stage={"Closed-Won"}>}[Stage Duration])/(count({<Stage={"Closed-Won"}>}[Stage Duration]) + count({<Stage={"Closed-Lost"}>}[Stage Duration]))

May be correct the Cases & spaces I have used in above expression

Re: Hit rate calculation

Wed, 11 Jan 2017 17:08:33 GMT

thanks for your answer.

where is this expression put ?

2017-01-11 18:02 GMT+01:00 Krishnapriya Arumugam <qcwebmaster@qlikview.com>:

Re: Hit rate calculation

johnw — Wed, 11 Jan 2017 17:21:20 GMT

Or since A/(A+B) = 1/(1+B/A):

1/(1+count({<Stage={'Closed - Lost'}>} [Stage Duration])/count({<Stage={'Closed - Won'}>} [Stage Duration]))

Arguably less clear, I suppose, even if more concise.

Edit: Oh, clearer and nearly as concise if we just do the + as a union:

count({<Stage={'Closed - Won'}>} [Stage Duration])/count({<Stage={'Closed - Won','Closed - Lost'}>} [Stage Duration])

Re: Hit rate calculation

johnw — Wed, 11 Jan 2017 17:23:53 GMT

Set analysis as shown above is fine, or you could move the complexity to the script with 1/null counter fields:

if(Stage='Closed - Won',1) as [Closed Won],
if(match(Stage,'Closed - Won','Closed - Lost'),1) as [Closed Won Or Lost],

And have a simpler chart expression:

sum([Closed Won])/sum([Closed Won Or Lost])

Re: Hit rate calculation

Wed, 11 Jan 2017 17:27:45 GMT

I have to put this expression on the scripting or? Somewhere else?

2017-01-11 18:22 GMT+01:00 John Witherspoon <qcwebmaster@qlikview.com>:

Re: Hit rate calculation

johnw — Wed, 11 Jan 2017 17:27:56 GMT

As an expression in whatever chart or other object you want to see the resulting percentage in.

Re: Hit rate calculation

krishnacbe — Wed, 11 Jan 2017 17:31:41 GMT

You need to put the expression in Text box or Chart.

Re: Hit rate calculation

krishnacbe — Wed, 11 Jan 2017 17:58:13 GMT

You need to create a Text box in your Sheet and put the expression.

ex:

1. Right Click on Sheet -> New Sheet Object -> Text Object

2. Type in the expression in the Text area. Prefix your expression with = sign

Re: Hit rate calculation

Fri, 13 Jan 2017 13:27:15 GMT

Hello ,

First of all, i would like to thank you for your support on my last

question.

Here i my new concerne, i have this set of data :

For each number in column 1, i have lots of item on the other columns

I would like to perform de carculation column 4*column 5 for ieach item of

colum 1 .

then have this item in a table.

my proble id to perform select the item in column 1?

Thanks for your support regards.

Cordialement,

2017-01-11 17:26 GMT+01:00 Krishnapriya Arumugam <qcwebmaster@qlikview.com>:

Re: Hit rate calculation

Fri, 13 Jan 2017 13:52:02 GMT

thanks for your answer