Using aggr in pivot table

[Anonymous]

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

[simenkg]

aggr(NODISTINCT sum(sales), country)

or

Sum(total <country> sales)

[PrashantSangle]

Hi,

just use sum() before your expression.

like

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

Regards

[tresesco]

May be like:

       = Sum( total <country> sales)

[SreeniJD]

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

[tripatirao]

Hi

go through this link

QlikView Technical Brief - AGGR.docx

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

[#7]

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

[mayankraoka]

Hi JD,

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

Regards,

Mayank

[simenkg]

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)