13 Replies Latest reply: Feb 19, 2018 11:36 AM by Carlos Londono

# Search Expression

Hi All ,

I have table with 3 dimensions:

Month

Client

Country

And 2 measures:

Sales

Number of products

Let's say i have 100 rows in this table.

Each row contains specific match and the numbers are different.

Now i have chosen  the current month.

I would like to use the search expression in the Country column to filter sales above 200,000\$.

When i use =sum(Sales)>200,000 it shows the total per country meaning sum of all combinations that above 200,000\$ but it is not what i would like to get.

How can i use the search expression the get all combinations in the table using the existing dimensions to get sales>200,000\$.

For example :

Client a          FR        203,456\$

Client b          DE        567,999\$

Client c          US        900,233\$

And not :

Client a          FR        203,456\$

Client b          DE        567,999\$

Client c          US        900,233\$

Client d          FR        115,745\$

Client d should not be in the selection.

Best,

Tal

• ###### Re: Search Expression

Hi Tal,

You can use set analysis:

Sum({<Sales = {">= 200000"}>}Sales)

Thanks,

Carlos

• ###### Re: Search Expression

Hi Carlos ,

It works well with one dimension but when i have 2 dimensions and more it still summarized the total and not the specific row .

Hope it is clear enough.

Do you have any suggestions?

Best,

Tal

• ###### Re: Search Expression

Can you share the expression you are using and the kind of chart you are applying it to?

I tried the expression and it works fine regardless of the filters you are applying.  If you want to include 200,000 and greater  use:

Sum({<Sales = {">= 200000"}>}Sales)

If you want just greater than 200,000 use:

Sum({<Sales = {"> 200000"}>}Sales)

If you share the expression you are using and the chart it is been applied to it would be easier to troubleshoot.

A Primer on Set Analysis

Thanks,

Carlos

• ###### Re: Search Expression

Hi Carlos ,

So it seems to work fine but with one exception.

when i select the current Month and specific Client but he has 2 different rows with different numbers beacouse

he spent it on product a and product b.

With the case above the expression: Sum({<Sales = {"> 200000"}>}Sales) will check the total sales of country and then check if there sum is greater then 200,000\$

I read it before i uploaded my q. It helped me in another case:)

I use the table option and as you well aware the search expression shows only in the dimension column and not in the measure column.

Appreciate it!

Best,

Tal

• ###### Re: Search Expression

I think I understand your issue, you need to add a column for product in your table, or if you are only interested in a single product you can add the filter for product in your expression.  Something like:

Sum({<Product = {'a'},Sales = {"> 200000"}>}Sales)

• ###### Re: Search Expression

Wow great ,

But if i don't show the the product column?

• ###### Re: Search Expression

Yes, you don't need to show the product column and still apply the product filter. If you find this to be an appropriate solution please mark it as the correct answer.

• ###### Re: Search Expression

Try :

sum({<Client={"=sum(Sales)>=200000"}>}YourMeasure)

• ###### Re: Search Expression

or (think it's more intelligent to do as follow)

sum({<Client={"=sum({1}Sales)>=200000"}>}YourMeasure)

• ###### Re: Search Expression

Thanks Omar!

I want to cover all options, means that if i'm not filtering month the following can happen:

Client a          FR        203,456\$    for Jan

Client a          FR        137,999\$    for Feb

Client c          US        900,233\$

and then when i use your expression it is still not filtering Feb but summarize and the 2 rows for FR appears.

Best,

Omri

• ###### Re: Search Expression

maybe:

Sum({<measure={">=200000"}>}measure)

• ###### Re: Search Expression

Hello bi_analysts_2you need to put your expression on column measure sales as follow :

sum({<sales={">200000"}>}sales)

i tried it with sample data and it works when i choose a specific month and country also