Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

gerald_t
New Contributor

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:

GroupVolumeInvestmentCOSASP
A100012005060000
B1500300070315000
C17503800100665000

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

1 Solution

Accepted Solutions
kuba_michalik
Contributor III

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

2 Replies
kuba_michalik
Contributor III

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

gerald_t
New Contributor

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!

Community Browser