Market Share

[emagos]

Hi all!

I'm a newbie in Qlik Sense and need some help to calculate the market share of some products. I have already searched in the documentation and Forum, but none of the solutions worked for me.

I have three tables:

1 - Sales - This table have the date and amount of each product that have been sold

2 - Products - This table have the name of the product

3 - Segments - This table have the name of the segment

I need to calculate the Market Share of each product in a certain segment. This share must be shown in a timeline, within a line chart, to see the changes across it.

The expression that got closest to helping me was:

=SUM(Sales)/SUM(ALL{<Segment={'A'}>}Sales)

But the second SUM is considering the total sales of the segment in the specified range of time, instead of considering the total sales of the segment for each month.

[rwunderlich]

I don't think you should be using the ALL keyword here.  What you want is the TOTAL keyword like this:

=SUM(Sales)/SUM({<Segment={'A'}>}TOTAL<Month> Sales)

where "Month" is the name of your time Dimension field in the chart.  I assume your other dimension is Product. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[emagos]

It didn't work. It's still considering the sum of the entire date range as the segment total. I've tried TOTAL before, it did the same.

I am using a Master Item Dimension to summarize the date by month, not sure if this may be the cause. The expression for the Dimensions is:

=MakeDate(Year(Date), Month(Date))

[rwunderlich]

You may be able to use the Dimension name in the TOTAL<dimname>. I haven't tried that. If that doesn't work, using Aggr might.

=SUM(Sales)/Aggr(SUM({<Segment={'A'}>} Sales),"=MakeDate(Year(Date), Month(Date))")

I wouldn't recommend this approach as a permanent solution (see https://www.naturalsynergies.com/q-tip-25-fields-on-the-fly-but-will-they/).  Better to create this Month field in the script and use TOTAL.

-Rob

[emagos · Accepted solution]

Thanks for the help, but it didn't work as well.

I tried loading the Year and Month separately as you recommended, but it does not calculate the total for each line, so it does not calculate the share for all the different products, just for a single one per month.

But besides the Year and Month loading, I joined all the tables in a single one and then used:

=SUM(Sales)/Sum(TOTAL <Segment, Year, Month> Sales)

This worked for me.