Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
dadumas
Contributor II

Set analysis intersect is not working.

Hi,

I am trying to show a chart of all customers that have been billed between $5000 and $10000.  I have variables for the hard coded below, but illustrating here with hard coded amounts.  The * operator is supposed to intersect both searches, like a sql intesect.  The lower bound is not working.

I can get them to both work independently of each other but I need it to work together "between lower and upper".

I have tried: 

sum({$<[Customer ID] = {"=[Billed Amount] >= 5000"}> * <[Customer ID] = {"=[Billed Amount] <= 10000"}>} [Billed Amount])

This one (above) is correctly filtering the chart on the upper bound (10000), but the lower bound is including customers with $0 [Billed Amount]).  So no errors, but it's not filtering the lower bound >= $5000

I have tried: (gets an error on a missing } )

sum({$<[Customer ID] = {"=[Billed Amount] >= 5000"}>} * {<[Customer ID] = {"=[Billed Amount] <= 10000"}>} [Billed Amount])

I have tried: (gets an error on a missing } )

sum({$<[Customer ID] = {"=[Billed Amount] >= 5000"}>} * {$<[Customer ID] = {"=[Billed Amount] <= 10000"}>} [Billed Amount])


Any ideas?


Dave

Tags (1)
1 Solution

Accepted Solutions

Re: Set analysis intersect is not working.

Why don't you just do this

Sum({$<[Customer ID] = {"=Sum([Billed Amount]) >= 5000 and Sum([Billed Amount]) <= 10000"}>} [Billed Amount])

6 Replies
m_perreault
Contributor III

Re: Set analysis intersect is not working.

sum({<[Billed Amount] = {">=5000<=10000"}>}[Billed Amount])

dadumas
Contributor II

Re: Set analysis intersect is not working.

I probably did not explain well enough.  filter needs to be the sum([Billed Amount]) between 5000 and 10000.  Your solution would only filter for any single transaction between those ranges.

Dave

mike_garcia
Valued Contributor

Re: Set analysis intersect is not working.

Try with:

sum({$<[Customer ID] = {"=Sum([Billed Amount])>=5000"} * {"=Sum([Billed Amount])<=10000"}>} [Billed Amount])



Miguel García
Qlik Expert, Author and Trainer
dadumas
Contributor II

Re: Set analysis intersect is not working.

This one works, (but Sunny's below also works and  is simpler):  It is very valuable, as it lets you solve these types of range questions.  I created variables for the ranges also, as input boxes for the user.

sum({$<[Customer ID] = {"=sum([Billed Amount]) >= 5000"}> * <[Customer ID] = {"=sum([Billed Amount]) <= 10000"}>} [Billed Amount])


The reason I thought it was not working was because I had other measures in the chart that were not filtered with:

<[Customer ID] = {"=sum([Billed Amount]) >= 5000"}> * <[Customer ID] = {"=sum([Billed Amount]) <= 10000"}>


As such, they were displaying correctly, but the one using this filter, was showing zeros for many rows, because it was sharing data across the straight table.  That was actually correct, but misleading.


It's critical that if you want to show other measures, you must use the same portion  <> * <> of the set analysis.

Re: Set analysis intersect is not working.

Why don't you just do this

Sum({$<[Customer ID] = {"=Sum([Billed Amount]) >= 5000 and Sum([Billed Amount]) <= 10000"}>} [Billed Amount])

dadumas
Contributor II

Re: Set analysis intersect is not working.

This also works, and it's a simpler formula than the one I used.  I did not know you could use AND within a search string.

Thanks!