Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Is It Possible filter in one expression with the value got it in other one?

Regards,

I want to apply a filter in one expression with the value got it in other one.

For example I have a expression like: fractile(AMOUNT,0.95)

And in other expression I want to know how much registers there are greater than the value get previous.

I have this: =Aggr(count({<AMOUNT={'>=$(=fractile(AMOUNT,0.95))'}>}ACCOUNT_NUMBER),SIC)

The value that I am getting is greater than the total fracile of the chart, and not with the greater than the dimension.

1 Solution

Accepted Solutions

Re: Is It Possible filter in one expression with the value got it in other one?

Script changes:

Authorizations:

LOAD RowNo() as UniqueIdentifier,

     AMOUNT,

     CONFIRMED_FRAUD,

     SIC,

     SIC_MCC

FROM

(qvd);

Expressions:

1) =Sum(Aggr(If(AMOUNT >= Fractile(TOTAL <SIC, SIC_MCC> AMOUNT,0.95), 1, 0),SIC, SIC_MCC, UniqueIdentifier))

2) =Sum(Aggr(If(AMOUNT >= Fractile(TOTAL <SIC, SIC_MCC> AMOUNT,0.99), 1, 0),SIC, SIC_MCC, UniqueIdentifier))

You may already have a uniqueidentifier field in which case you won't need to create a new one. But if there isn't one then you can use RowNo() to create one.

With Selection

Capture.PNG

Without Selection

Capture.PNG

19 Replies

Re: Is It Possible filter in one expression with the value got it in other one?

Try this using if statement:

=Count(Aggr(If(AMOUNT >= Fractile(AMOUNT, 0.95), ACCOUNT_NUMBER), SIC))

Not applicable

Re: Is It Possible filter in one expression with the value got it in other one?

Thanks. But it doesn't work, It shows me zero.

Re: Is It Possible filter in one expression with the value got it in other one?

What is your chart's dimensions and what dimension are you trying to check the Fractile information on?

Not applicable

Re: Is It Possible filter in one expression with the value got it in other one?

My dimension is a Category (Code), and for each one I calculate the fractile in an expression, and in other one I want to know how much registers there are greater than that fractile value.

Re: Is It Possible filter in one expression with the value got it in other one?

I don't know how Category(Code) and register are related to the current expression, but try this may be

=Count(Aggr(If(AMOUNT >= Fractile(TOTAL <registers> AMOUNT, 0.95), ACCOUNT_NUMBER), Category (Code), registers))

Not applicable

Re: Is It Possible filter in one expression with the value got it in other one?

Sorry, For me registers mean how many transactions there are.

He are a print screen:

Dimension: SIC

Expression: P_95 = fractile(Amount, 0.95)

Expression: =Aggr(count({<AMOUNT={'>=$(=fractile(AMOUNT,0.95))'}>}ACCOUNT_NUMBER),SIC)

print.png

Re: Is It Possible filter in one expression with the value got it in other one?

I think this should work, if it doesn't would you be able to provide a sample?

=Count(Aggr(If(AMOUNT >= Fractile(TOTAL <SIC, SIC_MCC> AMOUNT, 0.95), ACCOUNT_NUMBER), SIC, SIC_CC, Amount))

Not applicable

Re: Is It Possible filter in one expression with the value got it in other one?

print.png

Re: Is It Possible filter in one expression with the value got it in other one?

May be this:

So in this example the count would be 21078? since second row is the only one where we have total fractile smaller than the row's fractile?

Community Browser