topic Re: Additional load script, equivalent of Excel helper column in QlikView

Additional load script, equivalent of Excel helper column

Anonymous — Thu, 26 Jan 2017 12:00:27 GMT

Hi All,

I have what I hope will be a relatively simple requirement, but can't seem to come up with a solution.

My data contains a list of products and a list of quotations that are split into three stages. Submitted, Quoted and Sold.

Up until now I've only ever been asked to average the actuals, so quoted / submitted as an example would give me a percentage of what we call quote rate.

I have now been supplied with a sheet that contains targets in a percentage format by product and each product has a different %.

What I need, I believe, is the equivalent of an excel helper column.

So if I had 100 submitted quotes and I only actually quoted 10 of these my quote rate would be 10%. If the target is 53% then I should have quoted 53 of these.

I need a new calculated field that turns the target % into an actual figure so that I can divide the actual results against a numerical figure so that if looked at as a total product view gives a working percentage across the board as well as on individual cases.

I hope I have been clear enough, any questions please let me know.

Re: Additional load script, equivalent of Excel helper column

vinieme12 — Thu, 26 Jan 2017 12:48:43 GMT

please provide some sample data in excel or a sample app showing expected o/p

Re: Additional load script, equivalent of Excel helper column

Anonymous — Thu, 26 Jan 2017 13:13:15 GMT

Above is a simplistic representation of what I have. The Quote Rate, Strike Rate and Conversion Rate for the actuals (left) are simple. Just divide Quote Sold / Quote Quoted and you have the result of conversion rate.

But the targets are in percentage form, so if we look at oranges to achive a 25% Quote Rate in Oranges, based on the actual figures we would need to have quoted 25 of the submitted quotes.

I need an expression, or load that creates a field that turns the % amount in the target into an actual number that I can divide against to get my percentate.

Also need to know how far off we are from our target, so if the target is 25% how much of that target have we hit.

Re: Additional load script, equivalent of Excel helper column

vinieme12 — Thu, 26 Jan 2017 13:15:41 GMT

can you post sample data in excel to demonstrate?

Re: Additional load script, equivalent of Excel helper column

sunny_talwar — Thu, 26 Jan 2017 13:18:53 GMT

In your example above, it seems that Product is the common field between the two data sources. So, you can join the databases in the script to do the required calculation in the script or leave them as separate tables and perform all your required calculation on the front end.

Re: Additional load script, equivalent of Excel helper column

Anonymous — Thu, 26 Jan 2017 16:57:12 GMT

Hey Sunny. The Product field is indeed a common denominator between the primary data source and the excel sheet that is being loaded with the targets.

What I am not sure on is how to do the required calculation.

Re: Additional load script, equivalent of Excel helper column

Anonymous — Thu, 26 Jan 2017 17:21:59 GMT

I'm slightly confused as to what you want your end result to look like.

Wouldn't you just do [Target Quote Rate]*[Quote Submitted]?

That would be your target Quote Rate actual number.

Re: Additional load script, equivalent of Excel helper column

Anonymous — Mon, 30 Jan 2017 10:30:41 GMT

A simple expression might do the answer.

Let's say the target % for a product on quote rate was 80%. If we had 100 quotes and we quoted 80 then we know that our quote rate is 80% and so has met the target.

But what I am not sure on is how to write the expression to work out, in a simple KPI form, where we are against all products against there targets as a total percentage.

The expression would have to be Quotes Quoted / Quotes Submitted which gives us a % and then that % figure needs to be compared against the target % amount.

So if we have 5 products, one product is hitting or even exceeding the target and the other 4 products the total amount of target achieved for all 5 products combined might be 76%.

Re: Additional load script, equivalent of Excel helper column

Anonymous — Tue, 31 Jan 2017 10:37:21 GMT

Hello, I cannot see how to add an excel file. Only picures and movies for some reason.

Scratch that, I can see the attach option now: