Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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.
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))
With the first: 9,209
With the second; the same 9,209
My friend, what do you think? It's possible to do this?
It would be difficult to suggest anything without looking at a sample? Can you provide one?
Preparing examples for Upload - Reduction and Data Scrambling
The expression is Tx_P_95 or Tx_P_99
If I select one SIC, the expression shows the correct value.
Thanks
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
Thank you Very Much my friend! It works perfect!!!
Regards
Awesome