Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
aggr(NODISTINCT sum(sales), country)
or
Sum(total <country> sales)
Hi,
just use sum() before your expression.
like
sum(aggr(sum(sales), country))
Regards
May be like:
= Sum( total <country> sales)
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
Hi
go through this link
QlikView Technical Brief - AGGR.docx
You will get information how to use aggr() in pivot table.
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
Hi JD,
Can you please explain the importance of nodistinct here in more details.
Regards,
Mayank
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)