Remedying Data Recording Issues on the Front End in Domo
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.
Best Answer
-
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,45Etc…
If I solved your problem, please select "yes" above
0
Answers
-
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,45Etc…
If I solved your problem, please select "yes" above
0 -
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.0 -
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:
If I solved your problem, please select "yes" above
0 -
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.0 -
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.
If I solved your problem, please select "yes" above
1 -
Okay, I am in the process of gathering that information. Will update if it works.
0 -
I believe it worked, with some filtering and a right join.
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive