Set analysis intersect is not working.

[dadumas]

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

[m_perreault]

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

[dadumas]

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]

Try with:

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

[dadumas]

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.

[sunny_talwar · Accepted solution]

Why don't you just do this

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

[dadumas]

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!