topic Re: Market Evolution Index Calculation in QlikView

Market Evolution Index Calculation

Anonymous — Tue, 27 Oct 2015 23:25:15 GMT

I need to calculate a market evolution index (EI 2014) based on the following data:

The (EI 2014) for the subgroup "DIRECT ACTING ANTIVIRALS" is calculated as follows:

EI 2014 = (100 + % Growth Rate '14 for the Subgroup) / (100 + Total % Growth Rate '14) * 100 which is

EI 2014 = (100 + 13.47) / (100 +26.76) * 100 = 89

A value under 100 means that the subgroup is growing less than the overall market.

How can i accomplish this with an expression in Qlikview?

Re: Market Evolution Index Calculation

swuehl — Tue, 27 Oct 2015 23:36:25 GMT

That's hard to answer without knowing how your expressions look like.

Assuming the percentages are really percentages, i.e. 13.47% is 0.1347, and assuming your % growth rate 14 column is based on a field [% Growth '14] in the data model, your expression could look like

= (1 + Only([% Growth '14]) ) / (1 + Avg(TOTAL [% Growth '14])) * 100

Probably my assumptions are all wrong...

Re: Market Evolution Index Calculation

MarcoWedel — Tue, 27 Oct 2015 23:36:36 GMT

please post a sample application.

regards

Marco

Re: Market Evolution Index Calculation

Anonymous — Tue, 27 Oct 2015 23:51:27 GMT

Thanks Swuehl

The [% Growth '14] is a calculated column

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 00:06:10 GMT

Many thanks Marco

i will create a sample application as the original one is too big.

Regards.

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 01:34:45 GMT

Marco

Please find attached sample application.

Regards.

Re: Market Evolution Index Calculation

manojkulkarni — Wed, 28 Oct 2015 05:13:45 GMT

try to use TOTAL in first 2 expressions to get the Total Growth.

Re: Market Evolution Index Calculation

Wed, 28 Oct 2015 05:17:09 GMT

Hi Christopher,

Try the below expression

=(100 +([% Growth '13]*100))/( (100 +((Sum (TOTAL {$<Year={2013},Month=>} QTY)/Sum (TOTAL{$<Year={2012},Month=>} QTY))-1)))*100

Regards

Anand

Re: Market Evolution Index Calculation

Wed, 28 Oct 2015 05:20:38 GMT

Hi Christopher,

Try the below expression

=(100 +([% Growth '13]*100))/( (100 +((Sum (TOTAL {$<Year={2013},Month=>} QTY)/Sum (TOTAL{$<Year={2012},Month=>} QTY))-1)))*100

Re: Market Evolution Index Calculation

Wed, 28 Oct 2015 05:25:18 GMT

Hi Christopher

Find the application with solution.

Regards,

Anand

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 05:28:41 GMT

Thanks Manoj

I do not understand your recommendation, can you give me an example expression with the QVW document atttached or just post the expression.

Regards

Re: Market Evolution Index Calculation

jonathandienst — Wed, 28 Oct 2015 05:41:03 GMT

You can't use an aggregation function on a column label like that. Use an Aggr() instead:

=Sum({<Year={2013}>} QTY) / Sum({<Year={2012}>} QTY) / Avg(TOTAL Aggr(Sum({<Year={2013}>} QTY) / Sum({<Year={2012}>} QTY), ATCLevelThreeCode))

Your expression simplifies to the above. Leave it as a fraction and format it to display as %.

Re: Market Evolution Index Calculation

manojkulkarni — Wed, 28 Oct 2015 06:01:07 GMT

find attached sample

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 07:11:32 GMT

Thank you Jonathan

The Evolution Index is an index number and therefore should not be formatted as a percentage. The Evolution Index was developed primarily to provide better understanding of the relative product/category and market movements over time. It results in 100, if, over a given period the product/category growth is the same as the market growth. A number less than 100 indicates that the product/category is weaker than the market growth, while a number greater than 100 indicates growth stronger than that of the market.

For Antacids the Evolution Index should be :

(100 + (-6.37)) / (100 + 14.12) * 100

= 82

Regards

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 07:17:22 GMT

Many thanks Manoj

The results from the attached applications give the wrong values.

For example, the Evolution Index for Blood Glucose lowering drugs, excl Insulins should be:

=(100 + (-9.23)) / (100 +14.12) * 100

= 80

as opposed to the figure of 90 shown in the application you recommended. Please se my comments on the Evolution Index above in my reply to Jonathan.

Regards

Re: Market Evolution Index Calculation

manojkulkarni — Wed, 28 Oct 2015 07:37:09 GMT

find updated qvw

Re: Market Evolution Index Calculation

Anonymous — Wed, 28 Oct 2015 12:33:54 GMT

Manoj

Thank you very much, this revised applications works well.

Regards

Re: Market Evolution Index Calculation

Anonymous — Thu, 29 Oct 2015 09:35:42 GMT

Thanks Anand, most appreciated