Remedying Data Recording Issues on the Front End in Domo

hunter_brown

I have a dataset with item names. These item names have a preassigned "price" to them that is not in the source data (although I know what they should be). What has happened is that some people (not all people) record their transactions all at once, combining what should be multiple revenue values into a single one.

Current back-end recording errors (x number of items times y price arriving at that final revenue number z):

z = x * y

What I need (assuming 'z' is the recorded final revenue number, x is # of items, and y is individual price):

x = z / y

I want to figure out how many of an item name would arrive at the recorded final revenue number, and then return the real number of item names that there should be. I just don't know how I would go about implementing this in Domo. I have deduced that I need some sort of map to assign individual prices to each item name, and then go from there.

ColemenWilson

This sounds like something better solved in the source system. Is it possible to prevent this type of activity from occurring?

Unless you know which items are included in the total revenue amount it would be very difficult, if not impossible to determine what items are included with only the total amount and number of items included. For example, if the total is 100 and there are 5 items the item makeup could be:
1. 20,20,20,20,20
2.10,10,10,10,60
3.10,20,10,15,45

Etc…

ColemenWilson

This sounds like something better solved in the source system. Is it possible to prevent this type of activity from occurring?

Unless you know which items are included in the total revenue amount it would be very difficult, if not impossible to determine what items are included with only the total amount and number of items included. For example, if the total is 100 and there are 5 items the item makeup could be:
1. 20,20,20,20,20
2.10,10,10,10,60
3.10,20,10,15,45

Etc…

hunter_brown

https://community-forums.domo.com/main/discussion/comment/73089#Comment_73089

Thank you for the question,

Yes it would absolutely be better resolved in the source system. However, that is not something that I can readily fix at the moment, as the proper accountability systems for recording information are not in place.

It may have been a bit convoluted from my message, as it is a difficult problem, but I do know the items that are included in each transaction. I do not have the number of items included. For example, if 2 of a certain item were sold on the same day, a person might record the item and say "$700" for an item that is priced at $350.

To reiterate, what I know are the prices for each item, the transaction amount, the date the transaction was completed, and the item type. I do not know how many of the transactions were completed on a given row, unless the price matches revenue exactly.

ColemenWilson

Okay, so there is never a mix of items sold in a single row of data? It is always a single item, but in any quantity? Is this what your data looks like:

So I sold 2 of both widget A and B, and you sold 1 of widget A and B.

Or does it look like this, same sales activity as the previous table:

hunter_brown

https://community-forums.domo.com/main/discussion/comment/73094#Comment_73094

I like how you used a sample Excel file to try to understand. It is very close to what I have. Here is my recreation.

Each state has different unit prices or other variables that contribute to the actual total revenue. I know what each of these variables are, but they (unit prices, etc.) are not in the dataset.

ColemenWilson

Thank you for sharing the sample data, that is helpful. What I would try is putting together a table of items and their associated prices per state (this could be done with a Domo webform or Excel File upload), then join that with your data provided above on item name and state (and date because I am sure the prices change over time). That would give you Unit Price (column E in your sample data). You could then create a beast mode or use a formula tile in Magic ETL to derive unit (transaction) quantity: Revenue/Unit Price.

hunter_brown

https://community-forums.domo.com/main/discussion/comment/73099#Comment_73099

Okay, I am in the process of gathering that information. Will update if it works.

hunter_brown

I believe it worked, with some filtering and a right join.