Approval Rate/Amount calculated field

Question & Answer
, ,
1

Hello Team,

Could you please help me create a calculation field for AR%

I have field state_to (status) and order_id (count)

image
image169×313 7.56 KB

And payment_method (type of cards)

image
image153×356 6.59 KB

I want to calculate AR for specific payment_method

For example:
payment method visa
captured/captured+declined = 0,65%

Additionally, I have calculated field Convert to EUR

image
image335×311 5.05 KB

How to transfer each state_to to Convert to EUR ?

Thank you.

2

Hi @weird_guy,
Would you be able to elaborate a bit further on what you are trying to do or let me know if I’m headed in the right direction..
So essentially, for a given payment_method, you’d like to run the sum of ‘captured’ amounts (for ‘visa’ for example), divided by the sum of ‘captured’ (for visa)+‘declined’ (for visa)?

Additional questions:

  • Will the user have an option of selecting the payment method so it will vary based on selection?
  • When you ask how to transfer ‘state_to’ to ‘Convert to EUR’, what are you trying to do with this and how would you like ‘Convert to EUR’ to come into affect?
1 Like
3

@Brett Hello there
Thank you for your response.

So yes, Id like to run the sum of ‘captured’ amounts, divided by the sum of ‘captured’ +‘declined’ and convert it to percent
F.e., we have “captured” 150, “declined” 70. total 220.
150/220 = 0.68
in percent it is 68%

  • Will the user have an option of selecting the payment method so it will vary based on selection? - No, it happened. Just the finished data already with the completed purchases

  • When you ask how to transfer ‘state_to’ to ‘Convert to EUR’, what are you trying to do with this and how would you like ‘Convert to EUR’ to come into affect? - here I want to check how many funds are lost (DECLINED) / successful (CAPTURED ) in EUR.
    Aha, I got it. Actually I have additional field for each transactions “amount” and “order_id”
    So the formula is “status_to” + “order_id” (how many CAPTURED/DECLINED trx) * “amount”
    Then * “Convert to EUR” (because I have another filed “currency” . There are a lot of different currencies)

Hope it helps?

4

@Brett Hello
Please specify do you have update on this?
Thank you for your help.

5

Hi @weird_guy,
Apologies for missing your last response.

You could explore the sumIf function, although from my experience, this doesn’t work as well when needing to partition.

So instead, the best work around may be something along the lines of nesting sumOver functions within an ifelse statement.

It may look something like:

sumOver(ifelse({state_to} = ‘CAPTURED’, {sales}, NULL), [{payment_method}], PRE_AGG)/
(sumOver(ifelse({state_to} = ‘CAPTURED’ OR {state_to} = ‘DECLINED’, {sales}, NULL), [{payment_method}], PRE_AGG)

Then you can use a table calculation to make it a percentage:

image
image352×312 14.1 KB

Let me know if this works for your case or if you have any additional questions!

1 Like
6

@Brett Hello
Thank you for your response.

Ive faced with tech difficulties:

image
image1185×130 5.81 KB

How can I fix it?

P.S.: I am having trouble understanding the QuickSight language. Do you know of any tutorials or similar languages?
Thank you in advance!

7

Hi @weird_guy,
In reference to the QS language, unfortunately there’s no documentation that breaks down the error codes you may receive (in my opinion, the error messages could use some updating to help users diagnose the issue better!). In terms of help for learning more about how to better build and utilize the advantages of calculated fields, this is the best article I’ve found for furthering that knowledge!

Looking at your calculation; what is the field type for ‘order_id’, is it a string? If so, I believe that’s where your error is coming in as you’re unable to sum string values. Do you have an additional field in your dataset that contains the amounts you’re trying to sum or are you trying to get gather a count of order id’s?

If trying to utilize a count of order id’s in the calculation, you’ll half to first build out a separate calc. field to complete that; which in that case, I would check out the DistinctCountOver function.

8

@Brett

Hello Brett.

Thank you for your understanding. I’m just new to QS.

I just changed {sales} to {order_id}

In my example, order_id refers to transaction ID - and each order_id has a status of “CAPTURED” or “DECLINED”.
So I’m trying to calculate the approval percentage for each {payment method}.

For example:

A company has had 100 VISA card orders in the last 2 hours.
50 captured/ 50 declined= AR% is 50%.
OR
IF {payment method} = ‘VISA’ THEN ‘ApprovalRate’ = {CAPTURED} {order_id} / {CAPTURED} {order_id} +{DECLINED} {order_id} *100%

image
image628×430 27 KB

9

Hi @weird_guy,
So since you need to run off a count of your order id’s, why don’t we try this instead:

DistinctCountOver(ifelse({state_to} = ‘CAPTURED’, {sales}, NULL), [{payment_method}], PRE_AGG)/
DistinctCountOver(ifelse({state_to} = ‘CAPTURED’ OR {state_to} = ‘DECLINED’, {sales}, NULL), [{payment_method}], PRE_AGG)

Since you’re trying to gather a count of a field that’ s a string, you’ll need to incorporate a calculation that provides you with a count, like distinctCountOver.
Additionally, please check out this article as it explores the order of operations when building calculated fields and can be very valuable in helping out further.