Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
May be this
Sum({<Status = {'won'}>}Value)/Sum({<Status = {'discontinued', 'lost'}>}Value)
May be this
Sum({<Status = {'won'}>}Value)/Sum({<Status = {'discontinued', 'lost'}>}Value)
You use set expressions:
See the attached app.
If you are not familiar with set analysis, please refer to this thread:
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!
Your attached sample data has a field called Value
Do you call it differently in your actual database? May be Sales or Amount?
Ah OK yes, its not set to that in the actual DB, I will try it
Okay
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?
Ignore date field in set analysis
Sum({<Status = {'won'}, Date>}Value)/Sum({<Status = {'discontinued', 'lost'}, Date>}Value)
Sum({<YourDateField=, Status = {'won'}>}Value)/Sum({<YourDateField=, Status = {'discontinued', 'lost'}>}Value)