Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

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

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.

1 Solution

Accepted Solutions
Highlighted
Not applicable

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

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

View solution in original post

15 Replies
Highlighted
chiru_thota
Valued Contributor

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

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

QlikView Challenge (Products Purchased and Not Purchased Accounts)

Highlighted
Not applicable

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

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

View solution in original post

Highlighted

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

Hi,

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

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

Thanks & Regards

Highlighted
MVP
MVP

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

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

Highlighted
Not applicable

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

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.

Highlighted
Not applicable

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

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

Highlighted
Not applicable

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

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.

Highlighted
Not applicable

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

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!

Highlighted
Not applicable

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

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