Calculating a HitRate for various quotation status

[#1]

Hello community,

I'm fairly new to Qlikview and I try to create an analysis for the quotation processing times within our business unit.

My boss wants to see a HitRate for our quotations which we analyse in two ways:

1. the amount oriented HitRate:

--> amount of won quotations / (amount of won quotations + amount of lost quotations + amount of potentially lost quotations)

2. the value oriented HitRate:

--> summarized value of won quotations / (summarized value of won quotations + summarized value of lost quotations + summarized value of potentially lost quotations)

My problem needs a bit of background information explanation. Unfortunately, we have a huge amount of badly maintained data sets in our database (SAP). We need a "potentially lost" status for our quotations due to the fact that a huge amount of them is just open (=we neither got a corresponding order to the quotation, nor was the quotation yet marked as rejected). Due to that, we had to defined the potentially lost status to get more accurate numbers: If the quotation creation date is older than three months and there is neither a corresponding order, nor a rejection, the quotation is potentially lost (but was never updated in the system because our case handler didn't ask or our client didn't respond).

For each data set (one line per quotation), I have this status field which defines the quote as WON, LOST, OPEN or POTENTIALLY LOST

Now, when calculating the aforementioned HitRates, i work with the following data and definitions in my table:

Amount oriented data:

Quotations:          Count(QuotationNo)

Rejections:           Count(QuotationRFJ)

Rejections Real:   Count({<QuotationStatus ={LOST}>}QuotationNo)+Count({<QuotationStatus ={POTLOST}>}QuotationNo)

Orders:                Count (OrderNo)

HitRate Real:       Count({<QuotationStatus ={WON}>}QuotationNo)/(Count({<QuotationStatus ={WON}>}QuotationNo)+Count({<QuotationStatus ={LOST}>}QuotationNo)+Count({<QuotationStatus ={POTLOST}>}QuotationNo))

And the same, slightly changed for the value oriented comparison:

QValue:               Sum (QuotationValue)

OValue:               Sum (OrderValue)

RejValue:             Sum({<QuotationStatus = {Lost}>}QuotationValue)+(Sum({<QuotationStatus = {POTLOST}>}QuotationValue))

HitRate Real:        Sum({<QuotationStatus ={WON}>}OrderValue)/(Sum({<QuotationStatus ={WON}>}OrderValue)+Sum({<QuotationStatus ={LOST}>}QuotationValue)+Sum({<QuotationStatus ={POTLOST}>}QuotationValue))

This approach works nicely as long as I do not pre-select any data in any listbox of my dashboard. One listbox that I have is the QuotationStatus from above (won, open, lost, potentially lost).

Now when selecting data in the listbox, my HitRates are calculated incorrectly because with by using the way I used, the calculations for the HitRates always take the whole available data into consideration.

That means when I only want to look at won orders, the HitRate calculation should only count (or summarize) the values for the pre-selected category.

I thought I could do this with with the help of identifiers, but I either use the wrong one or I do something wrong.

I would really appreciate some helpful feedback resp. a hint what I am doing wrong.

Cheers,

Bastian

[#2]

Hi Bastian,

The reason that you aren't able to apply this filter is because in the set analysis you are using, you explicitly state the parameters for status, which over-rides the current selection. Unfortunately I don't know a way around this issue, maybe if you gave a little more information around what specifically you are after, there might be a creative solution.

As a suggestion to reduce the size of your expressions, although the syntax appears to be incorrect, you can use the "- " modifier to denote a NOT within set analysis. So rather than needing to list all the states you can go:

Count({<QuotationStatus ={WON}>}QuotationNo)/Count({<QuotationStatus = -{OPEN}>}QuotationNo)

Just for ease of checking, if you include the QuotationStatus in quotation marks, so QuotationStatus ={'WON'}, you don't get an error in the syntax checker, which can make it easier to check your results

Edit: Forgot to include, if you keep your text fields in quotation marks, you can use wildcard markers, so you can use QuotationStatus ={'*LOST'} to include both LOST and POTLOST in a single expression. Again not solving your issue but hopefully helping to reduce the size and complexity of your expressions.

Cheers,

Tyler

[Gysbert_Wassenaar · Accepted solution]

So if you select WON as category then for example Rejections Real should return value 0? If so try intersecting the hard coded set with the possible values resulting from your selection:

Count({<QuotationStatus ={LOST}*P(QuotationStatus)>}QuotationNo)+Count({<QuotationStatus ={POTLOST}*P(QuotationStatus)>}QuotationNo)

[#4]

Hello Tyler,

Thanks for your quick reply and the hint concerning the NOT modifier.

Concerning what I am after I though I already outlined that sufficiently

Well, I created a dashboard with the focus of analyzing quotation processing times. I depict how long it takes us to process a quotation. To make the data easier to compare I have a lot of categories and groupings such as: quotation value classes (value <=10k, <=30k, <=100k, <=1m, >1m), amount of material per quotation (<5 items, <20 items, <50 items, <100 items, >100 items) and I also have the aforementioned category for the quotation status (won = order to corresponding quotation existing, lost = offer rejected by client, open= no order and no feedback from client yet, potentially lost=no order and no feedback from client since 3+ months)

Now at a certain point when playing with the dashboard and selecting data, I also want to only look at won or lost, or potentially lost offers. I know that if I select only won quotations, my amount oriented HitRate will be 100% but I still want to be able to compare the value oriented HitRate. As correctly observed by you, the definitions which I wrote for the HitRates explicitly state to include all items into the calculation that have the specific status which I mentioned above. BUT, as soon as I choose for example only offers that are in the category >1m and I want to see only quotations that have a corresponding order (which equals the definition for my "WON" status), the calculation for the HitRate still incorporates all potentially lost offers. That it should only do as long as I incorporate all quotation status in my current data analyses (won, lost, open and pot. lost). But as soon as I choose specific states (maybe just won or just lost and potentially lost) the HitRates should calculate accordingly. At least that is what I am trying to achieve.

I am not really sure if this additional explanations helps. So maybe some example numbers to make it more concrete:

ALL quotation status selected:

100 offers, 50 orders, 10 rejected, 10 potentially lost, 30 open

Hit Rate is: 50/(50+10+10)= 71,4%

ONLY quotation status "WON" selected

50 offers, 50 orders (we are talking about the same data as above, but obviously we only have 50 offers left)

Hit Rate is now: 50/50=100%

This becomes even more important with the value HitRate:

Again, I only want to look at "WON" quotations to compare the quotation value and the actual order value (as it can happen that we give further reductions in the order).

Now again only these 50 offers should only be the basis for the calculation of the HitRate:

50 offers, value 50 million, 50 orders, value 45 million

Value HitRate: 45/50 = 90%

But my Hit Rate currently calculates this:

100 offers, value 100 million, 50 orders value 45 million, 10 rejection, value 10 million and 10 potentially lost, value 10 million

Value HitRate: 45/(45+10+10) = 63,2%

That calculation is correct, but it analyses all data in my data set (what it should do as long as I do not limit my selection by any grouping), instead of only calculating only with the "remaining" data.

I hope this additional description helps to understand what I am trying to achieve.

Thanks for any help.

Best regards,

Bastian

[#5]

Hello Gysbert,

Thanks for that reply.

This might be exactly what I need.

The addition *P(QuotationStatus) tells my calculation only to use the values that are possible within "QuotationStatus"? Is that it? So if I select "won" and "lost" as status, only the data that has either one of these two status is considered?

[#6]

That was already it. Quick and easy.

Thank you very much!