Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
isciberras
Contributor

sum the value of distinct orders

Hi All,

Would really appreciate some help as I am stuck. I would like to sum a column named values for all distinct orders.

OrderReferenceValue
12
12
23
3

4

Basically the values for each order reference will be the same so I just want to sum the value for each unique order reference.

Would anyone be able to help me please? Also sum (distinct OrderRefernece) won't work as some order references will have the same value as another order reference.

Thanks,

Isaac

1 Solution

Accepted Solutions

Re: sum the value of distinct orders

May be this

Sum(Aggr(Value, OrderReference))

8 Replies

Re: sum the value of distinct orders

What is the expected output for the data you have provided above?

isciberras
Contributor

Re: sum the value of distinct orders

9 would be my ideal answer basically (2+3+4) ...any suggestions would be greatly appreciated

Re: sum the value of distinct orders

May be this

Sum(Aggr(Value, OrderReference))

isciberras
Contributor

Re: sum the value of distinct orders

Thanks Sunny, this has been really helpful. Can I just confirm my logic as to what's happening here?

Are we aggregating order reference and summing the value?

Thanks again for your help, really appreciate it.

shanky1907
Contributor II

Re: sum the value of distinct orders

In Sunny's expression we are aggregating 'Value on the field OrderReference

effinty2112
Honored Contributor

Re: sum the value of distinct orders

Hi Isaac,

When you close the thread Sunny's answer should be marked as correct but in the meantime if I may I'd like to explain how the Aggr function is working here.

Aggr(Value, OrderReference) creates what may be described as an in-memory straight table with expression Only(Value) and dimension OrderReference. If you could see the table it would look like this:

OrderReference only(Value)
12
23
34

The Aggr function returns an array of values for Only(Value) - the values for each distinct OrderReference. You want the sum of these so we wrap the sum() function around the Aggr expression. It's a bit like setting the Total Mode in a straight table to the Sum of Rows:

OrderReference only(Value)
9
12
23
34

Just as you can set the Total Mode in a straight table to Avg, Min, Max, etc you can use the aggregation function of your choice to wrap around the Aggr expression.

Cheers

Andrew

isciberras
Contributor

Re: sum the value of distinct orders

Thank you Shashank Vyas, very helpful

isciberras
Contributor

Re: sum the value of distinct orders

Thank you Andrew, really appreciate your explanation