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

[Anonymous]

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

[kuba_michalik · Accepted solution]

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))

[Anonymous]

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!