topic Total Sales by Group (Product Share over specific total) in QlikView

Total Sales by Group (Product Share over specific total)

kira_whopper — Wed, 10 Aug 2016 03:35:44 GMT

Hi there,

I have a straight table where I need to calculate each Product's Share over it's Relevan Market.

A Product can be a part of one or more Relevant Markets, or Groups.

It's share is the sales / total sales of the groups it participates.

For example:

Product A sold $50 for Group1 and $100 for Group2.

Group1 total is $1,000 and Group2 total is $500

Product A Share is:

$150 / $1,500

I've tried to use Sum (TOTAL <Group> Sales), Aggr(Sum(Sales), Group), Aggr(NODISTINCT), etc.

Till now, no go.

Attached is a sample file with the problem.

Thanks in advance.

Re: Total Sales by Group (Product Share over specific total)

arulsettu — Wed, 10 Aug 2016 04:26:25 GMT

this one?

Re: Total Sales by Group (Product Share over specific total)

kira_whopper — Wed, 10 Aug 2016 05:02:14 GMT

Nope...
That's just the Product Sales / Group Sales Column...
But Group Sales Column is wrong...
For ProdA, should be Product Sales / Sum of Group1 and Group2 Sales

What I need is the right total for each row.

I added a new straight table with the right result for ProdA

Re: Total Sales by Group (Product Share over specific total)

arjunkrishnan — Wed, 10 Aug 2016 05:25:15 GMT

Hi Friend,

Try This Below Expression ,

Num(Sum(Sales)/Sum(Total <Product>Sales),'#,##0.0 %')

Re: Total Sales by Group (Product Share over specific total)

sunny_talwar — Wed, 10 Aug 2016 08:19:45 GMT

Try this expression to calculate the Group Sales:

Sum(Aggr(Sum(TOTAL <ProdGroup> Sales), Product, ProdGroup))

Re: Total Sales by Group (Product Share over specific total)

kira_whopper — Wed, 10 Aug 2016 11:23:15 GMT

That's exactly it!

Thanks a lot!