topic aggr with set analysis in QlikView

aggr with set analysis

Mon, 29 Jun 2009 13:48:19 GMT

Hi,

I'm using an aggr function to sum up my sales. Is it possible to combine it with set analysis so that it doesn't matter
what's selected?

Thanks

aggr with set analysis

Mon, 29 Jun 2009 22:08:21 GMT

Would this work for you? sum({1}field) This would disregard all selections providing they are not a dimension in your chart.

Regards

aggr with set analysis

stephencredmond — Mon, 29 Jun 2009 22:16:33 GMT

Yes, you can have a set in pretty much any expression, so it will work in Aggr:

max(aggr(Sum({ < SetField = {"value"} > } SalesValue, Country)))

Stephen

aggr with set analysis

Tue, 30 Jun 2009 16:12:47 GMT

Thanks Stephen,

unfortunatley I'm still having problems. I'm trying to see the max sales of (all) customers in Max Sales, in this case the value should

be 50.000, no matter what's selected.

I tried Max Sum Sales: max(aggr(Sum({ < Customer_ID = > } Sales, Customer_ID)))

should look like:

Customer ID Sum Sales Max Sum Sales Max Sales

1 10.000 50.000 60.000

2 35.000 50.000 60.000

3 10.000 50.000 60.000

4 40.000 50.000 60.000

5 60.000 50.000 60.000

Thanks

AW:Re: aggr with set analysis

Tue, 30 Jun 2009 16:50:56 GMT

I think you mean the max sales of all customers should be 60.000. Why do you want to use "Set Analysis"?

Try max(total aggr(Sum(Sales), Customer_ID)) ...

AW:Re: aggr with set analysis

Tue, 30 Jun 2009 16:59:45 GMT

There should be no changing in the max value (60.000) when selection a customer_id.

I thouhgt I could handle thos with Set Analysis.

AW:Re: AW:Re: aggr with set analysis

Tue, 30 Jun 2009 18:05:01 GMT

Ah ok, now i understand.The "max-value" should be independent to every filter.

In this case i would fix the values in the script when reloading the data.

aggr with set analysis

stephencredmond — Tue, 30 Jun 2009 19:14:44 GMT

Hi,

You need to put the set in both the Sum and the Aggr:

max

({<Customer_ID=>} total aggr(Sum({<Customer_ID=>} Sales), Customer_ID))

Stephen

aggr with set analysis

Tue, 30 Jun 2009 19:34:28 GMT

Hi Stephen,

in this case I only get values as long as I don't select a certain customer_id. In case of selecting a customer calculation is "-"

aggr with set analysis

Tue, 30 Jun 2009 20:49:52 GMT

It seems to me the Stephen's solution is good, also when I select a customer.

Try the equivalent

max

({1} total aggr(Sum({<Customer_ID={*}>} Sales), Customer_ID)) .

Roberta

aggr with set analysis

Tue, 30 Jun 2009 21:01:40 GMT

Thank you very much, this is working. So I need to use

Customer_ID={*} instead of Customer_ID=