Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I've been working in QlikView for a while now. I have a question about a certain column I've been working with. I have a table called 'Jobs' that counts all the jobs my company's won for our client. One of the columns is Status. Status has three possible values: Lost, Won, and Inactive.
I've been asked to create a bar chart that measures Won jobs vs the other two possible values (Lost and Inactive) by Salesperson. Can anyone help me with an expression I can use for this? I need to somehow combine Lost and Inactive Jobs into one bar.
The expression would be something like
NumericCount(Status, if(Status === 'Lost' OR Status === 'Inactive), 'Other Jobs', 'Won Jobs')
but it doesn't seem to be working.
Here is the solution where script modifications are made:
Jobs:
LOAD *,
If(Status = 'Won', 'Won', 'Rest') as New_Status;
load * inline [
Job,SalesPerson,Year,Status
1,AA,2014,Won
2,AA,2014,Lost
3,BB,2014,Won
4,CC,2014,Inactive
5,CC,2015,Won
6,AA,2015,Lost
7,CC,2015,Won
8,BB,2016,Inactive
];
Yes this is the method(doing most part at the script level) that i would also prefer.
using Aggr() - i am grouping with respect to 'Sales person' else count funct returns other values depending on the selections made.