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.
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
Try this using if statement:
=Count(Aggr(If(AMOUNT >= Fractile(AMOUNT, 0.95), ACCOUNT_NUMBER), SIC))
Thanks. But it doesn't work, It shows me zero.
What is your chart's dimensions and what dimension are you trying to check the Fractile information on?
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.
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))
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)
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))
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?