Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Sum values grouped by Id in pivot table

Hello, I have a set of data like that:
ID      Value

100     100 €

100     200 €

200     500 €

200     700 €

200     100 €

and I have a pivot table with a bucket of age group (<30, 31-40, 41-50, etc), so each ID belongs to a certain age group.

I would show, for every group age, the sum of the "Value" for age group. Since there are several ID with multiple "Value", I'd like to Sum the values that belongs to the same ID (assuming ID 100 as age 20, and ID 200 as age 50, I would show that the <30 Age group has 300 €, and the 41-50 Age Group has 1300 €).
I tried with the

Sum(aggr(Value,  ID))

and also with the

Sum(total<ID> Value).

but it isn't working at all.

Can you please help me?

Thank you!

6 Replies
Highlighted
Champion
Champion

Re: Sum values grouped by Id in pivot table

Hi,

try this: aggr(Sum(Value),  ID)

Highlighted
Creator
Creator

Re: Sum values grouped by Id in pivot table

Hi,

I think the problem might be the Euro Currency symbol try the below as an equation:

Aggr(Sum(num(left(Value,len(Value)-1))),ID)

OR you could change the field in the script to be:

num(left(Value,len(Value)-1)) as Value

Highlighted
Master III
Master III

Re: Sum values grouped by Id in pivot table

May be this

LOAD * Inline [
ID,Age
100,20
200,50
300,45
400,35]
;
LOAD * Inline [
ID, Value
100, 100
100, 200
200, 500
200, 700
200, 100
300, 500
400, 700
]
;

Highlighted
Contributor
Contributor

Re: Sum values grouped by Id in pivot table

Thank you all. Youssef, your solution doesn't work, every row has the "0" value now.
Aron, actually I don't have the Euro Currency symbol, I'm sorry I wrote it only for show that it is a numeric value.
Anyway, you also proposed the same functions of Aron that sadly it's not working.
Antonio, I can't use the LOAD inline because I've a big set of data, loaded from a SQL table.

Highlighted
Master III
Master III

Re: Sum values grouped by Id in pivot table

Domenico this is an example !

Can You provide sample data ?

Highlighted
Contributor
Contributor

Re: Sum values grouped by Id in pivot table

I solved. Since I'm using a pivot table, and the datas are already grouped, the Aggr function it's not necessary. A simple Sum(Value) works. It's curious, anyway, that the Aggr doesn't work in this case.

Thanks everybody