How can I ignore selections, sum excluding selected and sum selected in the same statement?

[#1]

Hi,

I am trying to build an expression that incorporates 1) ignoring selections in certain selection boxes, 2) summing sales for all possible values except the selected value for some selection boxes and 3) summing sales for the selected value for some selection boxes.  For example:

• MonthYear - Ignore any selection made in the MonthYear selection box  AND
• Site Location - Ignore any selection made in the Site Location selection box  AND
• Region - sum Sales for all Regions except the selected Region  AND
• Account Flag - sum Sales for the selected account flag  AND
• Customer Group - sum Sales for the selected customer group.

So, some selection boxes need an 'exclude selected' logic and some selection boxes need an 'include selected' logic.  Is this possible?

I've tried combining '1-$' and '$' identifiers in a set analysis expression unsuccessfully.  I appreciate your help with this!

Thanks,

Deniece

NOTE:  I'm editing this post to attach an example file of data that I would like to have the logic above applied to.  Thanks.

[maxgro]

Preparing examples for Upload - Reduction and Data Scrambling

[#3]

• Ignoring selections in MonthYear (this is the desired result)
• Ignoring selections in Region (this is not the desired result)
• Ignoring selections in SiteLocation (this is the desired result)
• Igoniring selections in AccountFlag (this is not the desired result)
• Ignoring selections in CustomerGroup (this is not the desired result)

not sure, it should work. i haven't inlcuded AccountFlaga nd CustomerGroup at all in my set, to select excluded thats the expression in the set Region=e(Region)

are you sure you are using your fieldnames as you have and no errors in the expression?

[#4]

Yes and Yes.    I'm selecting the fieldnames from the selection list and there are no errors in the expression.

[#5]

Moved the example file to the original post for ease of finding.

[#6]

i tested in your application below expression works as per your requirement

sum({<MonthYear,SiteLocation,Region=e(Region)>}Sales)

* unfortunately i cann't upload anything to external site. i just used the above expression

[#7]

Thanks.  This solution works today.  Perhaps I needed to reboot my machine yesterday because it was not returning the expected result yesterday.