Set Analysis - Ignore all selections except certain field

[#1]

Hello,

I'm trying to figure out how to calculate the sum of a measurement, disregarding the selections in all the fields except a certain field.

Let's assume the following fields:

Year, Person, Section, Sales. I would like to calculate the Sum of Sales, disregarding the selections at all fields but regarding the selections made at the "Person" field.

The expression:

SUM({1<Person=>}Sales)

Will not due the trick as the result will not change according to the selections made at the "Person" field.

Does anyone as a suggestion?

[#2]

Hi,

Try the following:

SUM({$<Year=,Section=,Sales=>} Sales)

Only selections on Person field will be taken.

Regards.

[spsrk_84]

Hi,

Try this way

SUM( {<Year = , Section=>}Sales)

Now it will not respect selections done on Year and section columns...........

I hope this what is required to you if not explain the problem once agin clearly..

Regards,

Ajay

[#4]

Hi,

Thanks for the qiuck reply.

This solution works fine, but it's a work-around that might get klutzy in case of many fields to be ignored. Is there any other solution which will require to indicate only the field "Person" in the Set Analysis formula?

[#5]

Hi again,

If you have a lot of fields you could try the following:

=Sum({1<Person={$(v_Person)}>} Amount)

Where the expression of the variable is:

=Concat(Distinct chr(39) & Person & chr(39),',')

I hope it helps you.

Regards.

** Corrected, thanks to Smakovskiy

[#6]

SUM({1<Person=$(=if(getselectedcount(Person,true())>0,'{'&chr(39)&GetFieldSelections(Person,chr(39)&','&chr(39),getselectedcount(Person,true()))&chr(39)&'}','')) >}Sales)

GL 🙂

[#7]

Migel, your way is good only if there is numeric field but when field is text, then your way will return null

[#8]

Right,

then you will have to use:

=Concat(Distinct chr(39) & Person & chr(39),',')

As well as you wrote in your expression.

Regards

[johnw]

Perhaps this. If I got the syntax right, it says to ignore everything but Person, and for Person, use the set of possible Persons. You might need P(Person) to tell it possible persons, but I don't think so. I think it figures out what you mean without that.

sum({1<Person=P()>} Sales)

[#10]

John, Migel, your variants are good but not always - I was solving such problem and tried all your variants. Both your ideas are right, but your ideas do not disregard all the other selections - if any selection in other field can have an influence on the possible values in this field - then your variant will show not the selected positions, but only possible inside the selection.

There is a problem of uploading an attachment - I've uploaded it to rapidshare - try it and you'll understand the difference.

http://rapidshare.com/files/437773891/9.qvw