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

[#1]

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.

[#2]

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)

[#3]

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.

[sunny_talwar]

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

[#5]

With the first: 9,209

With the second; the same 9,209

[#6]

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

[sunny_talwar]

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

Preparing examples for Upload - Reduction and Data Scrambling

[#8]

The expression is Tx_P_95 or Tx_P_99

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

Thanks

[sunny_talwar · Accepted solution]

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

[#10]

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

Regards

[sunny_talwar]

Awesome