topic Re: Set analysis problem in QlikView

Set analysis problem

Tue, 09 Sep 2014 07:58:02 GMT

Hi, I have a problem with the expression below. It basically gives me the account name with the best score to a set of questions in a particular industry.

It works fine until I make a selection on the account name field, when it only calculates it for that account name. But I have put "account name =" in the set analysis which should override the selection but it doesn't. I have also tried a 1 instead of a $.

max(aggr(sum(aggr([Savings]*((round(avg({$ <Industry = {'$(vIndustry)'},[Account Name] =>}[Question Response]))-1)/4),

[Question Number], [Account Name])),[Account Name]))

My options are to calculate it in the script or to make the account name selection a data island so I never make a real selection on that field. Any ideas?

Re: Set analysis problem

Gysbert_Wassenaar — Tue, 09 Sep 2014 08:18:29 GMT

Try adding a set modifier to the other aggregation functions too:

max({<[Account Name]=>} aggr(sum({<[Account Name]=>}aggr([Savings]*((round(avg({$ <Industry = {'$(vIndustry)'},[Account Name] =>}[Question Response]))-1)/4),

[Question Number], [Account Name])),[Account Name]))

Re: Set analysis problem

Tue, 09 Sep 2014 08:24:10 GMT

No it still only calculates on one account code sorry. I think it might be that the aggr function is using [Account name]. I am going to create a data island with all of the account names in and use that in the aggr function instead.

Re: Set analysis problem

Gysbert_Wassenaar — Tue, 09 Sep 2014 08:27:01 GMT

There was still an implicit aggregation in there. Change aggr([Savings] into aggr(only({<[Account Name]>}[Savings])

Re: Set analysis problem

Tue, 09 Sep 2014 08:33:12 GMT

Thanks Gysbert, that's brilliant - it works perfectly now.