Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Set Analysis that includes selections, and excludes others

I am trying to create a formula that will give me the total value of the sales for certain parameters. I want it to always give me the sum for the current year, a specific country, and a few other parameters, but I also want it to reduce it by the select partner. This is what I have so far, but it is giving me the sum for everyone, not limited to the selected partner, because of the {1} in the set analysis. It is important that the part I have in the set analysis is always there (regardless of selections), but how do I add in the selected partner?

=

sum({1<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])





Thanks for your help!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Set Analysis that includes selections, and excludes others


NaomiLyn wrote:it is giving me the sum for everyone, not limited to the selected partner, because of the {1} in the set analysis


So... don't use {1} in the set analysis? What am I missing? Is this not what you want?

sum({<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

If you really want it to ignore everything BUT the selected partner, there are ways to do that. The simple way is like this:

sum({1<[Partner]=P(),[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

But it's a bit TOO simple for many cases. It's looking at possible values, not selected values, and the possible values may be different than the selected values due to other selections. The other way is to check if selections have been made, if not search for "*" or nothing, and if so get the fields selections and insert them into the expression as the values to use. I can come up with the expression if necessary, but really, it seems like all you need to do is remove the 1.

4 Replies
Not applicable

Set Analysis that includes selections, and excludes others

Hello,

In the part where you're using Won={1}, RegSold={1}, just change it for Won=, RegSold=, for indicate that doesn't matter selections on these fields.

Tell me if it works please.

Not applicable

Set Analysis that includes selections, and excludes others

Those are actually values. I want to include any deals in the calculation where the flag for Won = 1, and the flag for RegSold = 1. Everything in the current set analysis has to be set, but I want the selection for Partner to come into the equation.

MVP
MVP

Set Analysis that includes selections, and excludes others


NaomiLyn wrote:it is giving me the sum for everyone, not limited to the selected partner, because of the {1} in the set analysis


So... don't use {1} in the set analysis? What am I missing? Is this not what you want?

sum({<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

If you really want it to ignore everything BUT the selected partner, there are ways to do that. The simple way is like this:

sum({1<[Partner]=P(),[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

But it's a bit TOO simple for many cases. It's looking at possible values, not selected values, and the possible values may be different than the selected values due to other selections. The other way is to check if selections have been made, if not search for "*" or nothing, and if so get the fields selections and insert them into the expression as the values to use. I can come up with the expression if necessary, but really, it seems like all you need to do is remove the 1.

Not applicable

Set Analysis that includes selections, and excludes others

The second option worked perfectly. Thank you

Community Browser