topic Re: Using aggr in pivot table in QlikView

Using aggr in pivot table

Anonymous — Thu, 24 Sep 2015 09:03:47 GMT

I have a pivot table with sales vs city and product. I want to add a new expressio wich shows sales by country.

My hierrachial leves are: country -> city.

I add this expression

aggr(sum(sales), country)

In the pivot table I have the sum of sales by country only for the first city of the group (the rest with -), , I want to show total by country for all cities

What can I do??

Thanks

Re: Using aggr in pivot table

simenkg — Thu, 24 Sep 2015 09:09:01 GMT

aggr(NODISTINCT sum(sales), country)

Sum(total <country> sales)

Re: Using aggr in pivot table

PrashantSangle — Thu, 24 Sep 2015 09:09:21 GMT

Hi,

just use sum() before your expression.

sum(aggr(sum(sales), country))

Regards

Re: Using aggr in pivot table

tresB — Thu, 24 Sep 2015 09:13:28 GMT

May be like:

= Sum( total <country> sales)

Re: Using aggr in pivot table

SreeniJD — Thu, 24 Sep 2015 09:15:11 GMT

Hi Juan,

As you might be aware, AGGR works as a Group by function and its expected behavior is to give results for the 1st row only. If you want to override it, have a "NODISTINCT" clause to it.. thats it

Thanks,

Sreeni

Re: Using aggr in pivot table

tripatirao — Thu, 24 Sep 2015 09:57:44 GMT

go through this link

QlikView Technical Brief - AGGR.docx

You will get information how to use aggr() in pivot table.

Re: Using aggr in pivot table

Thu, 04 May 2017 03:14:41 GMT

As a side note, if your aggr is calculating over integer fields on large data sets you will find performance is much higher, I have in data sets in the hundreds of millions and into the billions, found they can be much more efficient if i use integer representations of the field. For instance if Region is West, maybe adding a field used just for this where N, W, S and East are 1, 2, 3, 4 kind of thing .. display West but use the integer field in the AGGR, this is more noticeable when the dimension in question has many values. I have not tested using a dual() to assign an underlying integer to see if it has the same affect. ... just my 2 cents

Re: Using aggr in pivot table

mayankraoka — Sat, 26 May 2018 08:45:50 GMT

Hi JD,

Can you please explain the importance of nodistinct here in more details.

Regards,

Mayank

Re: Using aggr in pivot table

simenkg — Mon, 02 Jul 2018 08:42:17 GMT

if you have a table that looks like this:

load * inline [

YearMonth, Department, Sales

2018-jan, Toronto, 1000

2018-jan, New York, 1200

2018-jan, L.A., 1300

2018-feb, Toronto, 1100

2018-feb, New York, 2000

2018-feb, L.A., 700

];

Now you want to calculate what percentage of the total sales a department has contributed in a given month. So you want to calculate sum(Sales) / sum of total sales per month.

If you write Sum(Aggr(Sum(Sales),YearMonth)) as an expression in your chart, then the result will be:

YearMonth, Department, Sales, sum of total sales per month

2018-jan, Toronto, 1000, 3500

2018-jan, New York, 1200, NULL

2018-jan, L.A., 1300, NULL

2018-feb, Toronto, 1100, 3800

2018-feb, New York, 2000, NULL

2018-deg, L.A., 700, NULL

This is because the AGGR function is DISTINCT by default. It will only return one value for each value of YearMonth. In order for you to get a value for each value in YearMonth, you need to use the qualifier NODISTINCT.

If you write sum(Aggr(NODISTINCT sum(sales),YearMonth)) then the result will be:

YearMonth, Department, Sales, sum of total sales per month

2018-jan, Toronto, 1000, 3500

2018-jan, New York, 1200, 3500

2018-jan, L.A., 1300, 3500

2018-feb, Toronto, 1100, 3800

2018-feb, New York, 2000, 3800

2018-deg, L.A., 700, 3800

You can also get this result by simply writing sum(total <YearMonth> Sales)