Multifield Formula assistance

[TC101]

I am trying to build a workflow that will, among other things, look at a series of payments to clients and flags payments to clients for additional checks based on a specified criteria. I have managed to do this using formula but it is not a practical solution given the size of the data set. I have created the following data set based on the one I am working with:

Client ID	Jan_Pymt	Jan_Qty	Feb_Pymt	Feb_Qty	Mar_Pymt	Mar_Qty
CID01	25	5	5	0	25	6
CID02	25	5	20	1	15	2
CID03	50	9	50	5	10	0
CID04	0	2	20	0	5	0
CID05	25	5	5	5	5	5
CID06	25	0	10	8	15	10
CID07	25	4	10	6	10	10
CID08	25	0	0	1	20	10
CID09	25	3	50	4	20	0
CID10	50	2	20	0	25	5

Is it possible to use a Multifield Formula that will:

1. Add a new field that calculates the sum of the "Pymt" columns based on the value in the "Qty" columns. For example, sum the payments when the quantity = 0 (or if possible based on a ratio of the payment to quantity being less than a specified value).

2. Adds a new field that adds a flag "Check" based on the same criteria used for summing the payments.

The goal is being able to filter clients for checking and being able to use the value of the sum of payments to prioritise the order of the Clients for payment checks to be made.

[Luke_C · Accepted solution]

Hi @TC101 

With this dataset what would your expected result look like for your criteria? I don't think multi-field is the way to go. I'd suggest transposing the data, doing any filtering/logic, and summarizing back up by client ID.

[TC101]

I was hoping for the output to look something like this:

Client ID	Jan_Pymt	Jan_Qty	Feb_Pymt	Feb_Qty	Mar_Pymt	Mar_Qty	Sum of Payments	Check
CID01	25	5	5	0	25	6	5	Yes
CID02	25	5	20	1	15	2		No
CID03	50	9	50	5	10	0	10	Yes
CID04	0	2	20	0	5	0	25	Yes
CID05	25	5	5	5	5	5		No
CID06	25	0	10	8	15	10	25	Yes
CID07	25	4	10	6	10	10		No
CID08	25	0	0	1	20	10	25	Yes
CID09	25	3	50	4	20	0	20	Yes
CID10	50	2	20	0	25	5	20	Yes

Then we can filter and sort the out put based on the two additional new columns. I will give your suggestion a go.

[Luke_C · Accepted solution]

Hi @TC101 

Here's what I was envisioning:

• Transpose by client ID
• Split the headers by underscore
• Cross tab back to have client ID - Month - Payment - Qty
• Filter to 0 Qty and sum payment
• Join back to dataset and add check

[jirikrecek]

I've taken a crack at this:

- the gist of it is that I calculate a ratio  (as per your ask) and the formula tool (3rd one from the end) sets the check flag to CHECK based on 2 criteria

• there was zero quantity that month
• the ratio of payment vs quantity was less than 0.5 - you can set this to whatever you want

I did use the multirow formula for demonstration, but it is not necessary IMO

Next step would be for you to clean the final table to show what you need it to - I'd personally split this into 2 tables - one for zero QTY and one for low ratio - two different types of checks

Hope this helps!

JK>