Multifield Formula assistance
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
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).
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.
Solved! Go to Solution.
- Labels:
- Data Investigation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
![](/skins/images/F46203A7339C1798EB03AB2274277419/responsive_peak/images/icon_anonymous_message.png)