topic Min and max with aggregation and sum in QlikView

Min and max with aggregation and sum

Tue, 19 Oct 2010 16:02:23 GMT

Hi all QV users,

I have a following problem. I would like to show in a table like that:

the value of the lowest/ highest sales and the average of all sales (sum of sales). For example you can see that there are 9 customers tagged as a Blenders. Among them I would like to find a customer with the lowest sum of sales and the highest sum of sales (preferably showing the value & the name of the customer). I tried few agg and sum functions and unfortunatley I am totally stacked. I would appreciate your help.

Regards,

Beata

Min and max with aggregation and sum

Tue, 19 Oct 2010 16:13:41 GMT

Is your Total Volume something like Sum(Sales)? What is the name of the Core field?

You need an Aggr(), so it would be something like:

Min(Aggr(Sum(Sales), CustomerPri, CustomerType, Account)

Your Aggr() needs all the dimensions to group by, in this case, you want to stay within CustomerPri and CustomerType and find the Account with the lowest value.

Min and max with aggregation and sum

Tue, 19 Oct 2010 16:36:34 GMT

Works great!!! I tried with Min(Aggr(Sum(Sales), CustomerPriority, CustomerType) earlier but I was missing 'Account', what is just the CutsomerName in my data. Is there any chance to see the name of the lowest sum of sales?

Thank you very much,

Beata

Min and max with aggregation and sum

Tue, 19 Oct 2010 17:46:50 GMT

Yes, as I was typing up my response, I forgot the Account at first. I remembered just before hitting Post.

The name of that account may be a little more difficult, because the Sales per Customer is probably an expression (not a field), which makes it hard to use in Set Analysis. Maybe:

If(Aggr(Sum(Sales), CustomerPriority, CustomerType, Account) =
Min(Aggr(Sum(Sales), CustomerPriority, CustomerType, Account)), Customer)

I'm not sure if that's going to work though. This may be a case where some sample data is needed, because Aggr() can be a little tricky and is very dependent on the structure of the dataset.

EDIT: Maybe also try:

Concat(If(Aggr(Sum(Sales), CustomerPriority, CustomerType, Account) =
Min(Aggr(Sum(Sales), CustomerPriority, CustomerType, Account)), Customer))

That may be needed, because you could possibly return multiple.

Min and max with aggregation and sum

Tue, 19 Oct 2010 18:11:56 GMT

Hallo,

Thank you for your effort. Unfortunately both of the expressions doesn't work. Second one doesn't show any of the results, while the first one shows the correct name, in the case where there is only one account (one customer). I think there has to be some solution for that, as far as QV is able to recognize correct min (so knows min customer), it should be able also to show it's name. Anyway, thank you very much for your help!

Regards,

Beata