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.

[chiru_thota]

Have a look at below post.You can achieve it through variables and set analysis

QlikView Challenge (Products Purchased and Not Purchased Accounts)

[#3 · Accepted solution]

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

[its_anandrjs]

Hi,

By set analysis you can achieve this by ignoring or by pass the filter selection like

Sum({Year=,Month=,Division= }    SalesValue)

Thanks & Regards

[maxgro]

I think you already get the answers to your question

You can find a detailed explanation of previous suggestions in this very good doc (thanks to Aunez Fabrice)

Set Analysis: syntaxes, examples

[#6]

Hi,  This solution returns a static answer, i.e. when I make different selections in the QVW, I always get the same result.  The solution I am after will return a different result when I make different filters/selections in the QVW.

[#7]

sum({<MonthYear=,SiteLocation=,Region=e(Region)>}sales) - means, it respects all other selections those are not listed in the set, it ignores monthyear,sitelocation and selects excluded regions (those are not selected).

Can you please post your expression or sample app

[#8]

Hi,

This solution does not cover the requirement to sum all values EXCEPT the selected value for some selection boxes AND sum the selected value for the selected value in other selection boxes.

[#9]

Hi,

I cannot post my file due to confidential information, however, what the expression is currently doing is

• 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)

Here is the expression from my QVW:

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

Thanks for your assistance with this!

[#10]

Thank you for suggesting this.  I read this earlier and was unable to find a solution that covered all 3 components (ignore, sum except selected, and sum selected).