Remedying Data Recording Issues on the Front End in Domo

hunter_brown
hunter_brown Member
in Beast Mode

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.

Tagged:

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    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,45

    Etc…

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    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,45

    Etc…

    If I solved your problem, please select "yes" above

  • hunter_brown
    hunter_brown Member
    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
    ColemenWilson Coach
    edited August 2023

    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:

    Screen Shot 2023-08-03 at 1.36.37 PM.png

    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:

    Screen Shot 2023-08-03 at 1.38.31 PM.png

    If I solved your problem, please select "yes" above

  • hunter_brown
    hunter_brown Member
    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.

    image.png


    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
    ColemenWilson Coach
    edited August 2023

    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

  • hunter_brown
    hunter_brown Member
    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
    hunter_brown Member
    edited August 2023

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

Categories