topic Re: Market Share Equation in QlikView

Market Share Equation

magnificen7 — Fri, 29 Jan 2021 15:28:42 GMT

Hi,

I need help with a market share equation. I have one table that looks like the following.

Company/ Premiums Written/ Market share

The market share equation looks like the following and is calculating correctly.
=sum([Premiums Written Amount state])/aggr(NODISTINCT sum({<[Entity Name] = >} [Premiums Written Amount state]), [Year])

I have another table that is the following.

State/ Premiums written/ Market Share

I'd like to be able to select a company, and the market share calculates each states market share for just that 1 company being 100% versus all companies being the 100%.

For example if Chubb is selected and their total premium is $100, and their premium in CA is $10, I'd like to see 10% versus maybe 1% if the entire industry premium is $1000.

Hopefully that makes sense. If it doesn't I can try to explain more clearly.

Thanks in advance.

Re: Market Share Equation

edwin — Fri, 29 Jan 2021 17:08:59 GMT

if you require the user to select a company then you can do a simple divide by the total:

sum( [Premiums Written Amount state])/sum(total [Premiums Written Amount state])

obviously, it will be across ALL companies if no company is selected

Re: Market Share Equation

magnificen7 — Mon, 01 Feb 2021 14:27:10 GMT

Hi,

Thanks for responding! I tried that as well and it's not giving me an accurate market share by state. Is it because I have other metrics selected as well? Like the year?

This is what I'm getting with that equation. (attached picture) I would expect the market share for California to show 10.8%.

Do I need to add something in the equation for the year and for the state?

Re: Market Share Equation

edwin — Mon, 01 Feb 2021 14:53:05 GMT

its hard to say what is wrong when there is no visibility to what you are doing also your number (10.8%)does not match your picture as you are only showing part of the numbers. i used the same expression and im getting the rt numbers:

Re: Market Share Equation

magnificen7 — Mon, 01 Feb 2021 15:05:15 GMT

Hi Edwin,

Thanks again for getting back to me.

I realized I had this as my equation

=sum([Premiums Written Amount state])/sum({1} TOTAL [Premiums Written Amount state])

Instead of what you posted. I pasted in what you had and it worked. Thanks for your help!

Re: Market Share Equation

edwin — Mon, 01 Feb 2021 15:33:53 GMT