19 Replies Latest reply: Apr 28, 2016 4:24 PM by Sunny Talwar

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.

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

• 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?

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

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

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

• 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?

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

For example for SIC 5411, the correct value for V_P_95 is 16,259.

It means: 16,259 transactions which its amount is greater or equal to 237.13 (P_95)

Actually it compares versus 503.00 (P_95 total chart)

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

if I do this, Set up the value in the expression: count({<AMOUNT={">=237.13"}>}ACCOUNT_NUMBER)

I will get the correct value.

But I want it will work for all SIC based on P_95 of each one.

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

I see what you are trying to do.

Did this not work for you?

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

or this:

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

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

With the first: 9,209

With the second; the same 9,209

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

My friend, what do you think? It's possible to do this?

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

It would be difficult to suggest anything without looking at a sample? Can you provide one?

Preparing examples for Upload - Reduction and Data Scrambling

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

The expression is Tx_P_95 or Tx_P_99

If I select one SIC, the expression shows the correct value.

Thanks

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

Script changes:

Authorizations:

AMOUNT,

CONFIRMED_FRAUD,

SIC,

SIC_MCC

FROM

[C:\Users\llopez2\Desktop\LLopez Work\Data3.qvd]

(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

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

Thank you Very Much my friend! It works perfect!!!

Regards

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

Awesome