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

[#1]

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 ID	Status	Value
87645	won	3500.00
67542	won	5000.00
76547	lost	10000.00
96765	discontinued	30000.00
54362	lost	2500.00
45364	won	15000.00
95637	in process	40000.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!

[sunny_talwar · Accepted solution]

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

[#4]

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

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

[#6]

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

[sunny_talwar]

Okay

[#8]

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)

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