Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

May be this

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

View solution in original post

9 Replies
sunny_talwar

May be this

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

OmarBenSalem

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
Author

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!

sunny_talwar

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
Author

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

sunny_talwar

Okay

Not applicable
Author

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?

sunny_talwar

Ignore date field in set analysis

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

OmarBenSalem

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