topic Re: Aggregations problems in QlikView

Aggregations problems

Fri, 22 Nov 2013 19:52:57 GMT

Hello,

I have the sales for each salesman that belongs to a store in a particular region;

I would like to know what is the participation of each store in its region.

I created a table with dimension as store and expression as : =sum(Sales) / sum(Aggr( NODISTINCT sum(Sales), Region))

But I have wrong values, I get the following result :

Whereas the results should be :

Store 1 : 100%

Store 2 : 50%

Store 3 : 50 %

I have attached a file with my project.

Thanks for you help !

Sadjad

Re: Aggregations problems

Fri, 22 Nov 2013 21:28:25 GMT

Hi Sadjad

You are looking for the sales that each store has as a total of the region's sales - and it's not the aggr() function that you need.

Add the Region as a dimension (you can hide this later) and add the expression :

= sum(Sales) / sum(total <Region>Sales)

This will give you the store's sales as a percent of the total for the region.Is this what you are looking for?

Erica

PS you had "relative" ticked in the expression.This returns your value as a % relative to the total in the column, ie it will force that column to add up to 100%.This is useful, but not for your purpose

Re: Aggregations problems

MK_QSL — Sat, 23 Nov 2013 08:45:00 GMT

Try below code... This should work...

SUM(Sales) / Aggr(SUM({1}TOTAL <Region> Sales),Region,Store)

Re: Aggregations problems

Mon, 25 Nov 2013 12:39:45 GMT

Hi Erica,

Thanks for your answer.

If I hide the dimension Region in my table = sum(Sales) / sum(total <Region>Sales) gives me the total of sales for all Countries and I want the total for each country.

The one that seems to work is sum(Sales)/ sum(Aggr (sum(total<Region> Sales),Region,Store))

Re: Aggregations problems

Mon, 25 Nov 2013 21:09:00 GMT

Good to hear it worked