topic Re: Set analysis sum formula in QlikView

Set analysis sum formula

Thu, 21 Nov 2013 10:10:28 GMT

hello all,

I am trying to create a pivot table, showing the share of each customer's sales vs their region total sales.

The table should look like following

Region	Customer	Sales	Share
France	Customer 1	100	50%
France	Customer 2	100	50%
Germany	Customer 3	100	33%
Germany	Customer 4	200	66%
Italy	Customer 5	500	100%

Now I am not sure how to create the expression for the share field.

The challange for me is to calculate the total sales for each region as an aggregation of all customers sales per region.

Afterwards I would devide the individual customer's sales by that regional sales amount.

Here I got stuck. All the sum or aggr formulas I tried show basically the customers sales and not the regional sales amounts.

kaushiknsolanki — Thu, 21 Nov 2013 10:20:03 GMT

Hi,

Try this Expression.

sum(Sales)/Sum(TOTAL <Region> Sales)

Regards,

Kaushik Solanki

vikasmahajan — Thu, 21 Nov 2013 10:22:02 GMT

Create vTotalValue variable for total sales

and apply following formula for calculate % share

=SUM({$<FinancialYear=,CORPORATE=,Date={">=$(=max(YearStart))<=$(=max(YearEnd))"}>}Values/10000000)/vTotalValue

Hope this help you.

Vikas

Thu, 21 Nov 2013 10:58:51 GMT

Hi Kaushik,

this goes into the right direction but needs some adjustment.

So in general, when the pivot table is fully expanded, the values are fine.

However when I select one single customer from the pivot table the expression returns result, which is not wanted,e g.

Customer 1 selected

Region Customer Sales Share

France Customer1 100 100%

Here, I would have expected to still show the original share value (50%).

So in this case, the formula is aggregating the regional sales, based on the current selected customer.

Is there modification, I can apply to avoid this effect?