2 Replies Latest reply: Apr 23, 2013 10:10 PM by Gerald Tagudando

# Can we use an expression as {'filter'} for set analysis?

Hi Guys,

I would like to ask for your help regarding my problem. My client will be very happy if you can help me out.

Question: Can we use an expression as filter for set analysis?

To explain my question, here is an example:

 Group Volume Investment COS ASP A 1000 1200 50 60000 B 1500 3000 70 315000 C 1750 3800 100 665000

Formula:

ASP = COS * Volume * Investment / 1000

Objective:

To count the groups with ASP > 300000

The catch:

Each column came from different tables, they cannot be joined / concat

Ideally, this expression below should fix the issue:

Count ({\$<ASP={>300000}>} DISTINCT Group)

But as I said before, they came from different tables so using ASP =  COS * Volume * Investment / 1000 in the load script does not work.

This is actually what I have in mind although the output is wrong:

If (COS * Volume * Investment / 1000 > 300000, Count (DISTINCT Group))

Question:

We can use an expression in IF statement to filter the result but the question is: Can we use an expression as filter in a set expression?

Also, it would be great if you can give the correct expression to get the groups with ASP > 300000.

Thank you very much and Godbless!

Gerald

• ###### Re: Can we use an expression as {'filter'} for set analysis?

Basically you can't. Set analysis is for making/modifying selections at expression level, and you can't select an expression result. It has to be a field.

You can achieve what you want with advanced aggregation, like this:

```=Count(Aggr(If(Sum(COS)*Sum(Volume)*Sum(Investment)/1000>300000,1,Null()), Group))
```
• ###### Re: Can we use an expression as {'filter'} for set analysis?

That was smooth Kuba_Michalik I tried your suggestion and it worked!

And thank you for helping me understood that expressions can't be used as filter in set expressions.

Godbless you!