Solved: Re: Is It Possible filter in one expression with t... - Qlik Community

Report Inappropriate Content · ‎2016-04-27

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.

sunny_talwar · ‎2016-04-28

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

Without Selection

View solution in original post

sunny_talwar · ‎2016-04-27

Try this using if statement:

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

Report Inappropriate Content · ‎2016-04-27

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

sunny_talwar · ‎2016-04-27

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

Report Inappropriate Content · ‎2016-04-27

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.

sunny_talwar · ‎2016-04-27

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

Report Inappropriate Content · ‎2016-04-27

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)

sunny_talwar · ‎2016-04-27

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

Report Inappropriate Content · ‎2016-04-27

sunny_talwar · ‎2016-04-27

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?

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