Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

How do I create a KPI for Win/Loss ratio?

Hi All

I have a set of projects, some won, some lost, some discontinued and some in process.

What I need is a ratio of projects WON versus projects LOST or DISCONTINUED.

My data looks like this...

  

Project IDStatusValue
87645won3500.00
67542won5000.00
76547lost10000.00
96765discontinued30000.00
54362lost2500.00
45364won15000.00
95637in process40000.00

I need an expression for a KPI in the dashboard that sums up the WON, LOST, and DISCONTINUED, and then calculates the ration.

In excel I can do this with the SUMIF function. 

Any help would be much appreciated.

Thanks!

1 Solution

Accepted Solutions

Re: How do I create a KPI for Win/Loss ratio?

May be this

Sum({<Status = {'won'}>}Value)/Sum({<Status = {'discontinued', 'lost'}>}Value)

9 Replies

Re: How do I create a KPI for Win/Loss ratio?

May be this

Sum({<Status = {'won'}>}Value)/Sum({<Status = {'discontinued', 'lost'}>}Value)

Partner
Partner

Re: How do I create a KPI for Win/Loss ratio?

You use set expressions:

See the attached app.

If you are not familiar with set analysis, please refer to this thread:

YTD, MTD issue

Capture.PNG

Not applicable

Re: How do I create a KPI for Win/Loss ratio?

Hi Sunny

Qlik expression editor is returning "Bad field name: Value, Value" when I copied it, did I miss something? Should Value be substituted with something?

Thanks!

Re: How do I create a KPI for Win/Loss ratio?

Your attached sample data has a field called Value

Capture.PNG

Do you call it differently in your actual database? May be Sales or Amount?

Not applicable

Re: How do I create a KPI for Win/Loss ratio?

Ah OK yes, its not set to that in the actual DB, I will try it

Re: How do I create a KPI for Win/Loss ratio?

Okay

Not applicable

Re: How do I create a KPI for Win/Loss ratio?

Hi Sunny,

It works well but the only snag is that I think it needs to be limited so it does not change as I filter projects by date.

I kind of want it to ignore the date filters I use in the other parts of the dashboard.

The effect I see is that when I select recent projects, the KPI then recalculates the win loss ratio on these dates.

Did I explain that OK?

Re: How do I create a KPI for Win/Loss ratio?

Ignore date field in set analysis

Sum({<Status = {'won'}, Date>}Value)/Sum({<Status = {'discontinued', 'lost'}, Date>}Value)

Partner
Partner

Re: How do I create a KPI for Win/Loss ratio?

Sum({<YourDateField=, Status = {'won'}>}Value)/Sum({<YourDateField=, Status = {'discontinued', 'lost'}>}Value)