hunter_brown Member

Thanks for solving, had some duplicate names and unnecessary columns and John the dashboard dude helped me fix them. Thanks again!

@DashboardDude I would appreciate some more assistance on this to get to a unique key. I want to use negative line amounts from CC details to match to credits (so I would have to make credit negative), and the rest of the line amounts to line up to debits. Do you know how to tackle something like this?

So I have been validating back and forth, and it turns out that not every transaction id, GL and posted date are unique. What is happening is that post-ETL, the transactions are summed almost as if they had been grouped, even though I don't have a group by. So in the source expense file, there will be two transactions, one…

Hey thanks for the quick response. Your thinking of seperating and appending is what I am doing. The 0 and 1 is no and yes for a credit card transaction. This leads me to think my way of thinking about appending is wrong. The issue isn't with getting the details merged with the rest of the expenses so much as it is that…

@MichelleH Okay, thanks for your help.

@MichelleH Yes I know about that, however when I select a date in my dashboard that is before the last 13 months from the current date, nothing is visualized. I want to take a selected date in the dashboard view and see the last 13 months. For example, when selecting December 2022, show that as the last value, rolling back…

@MichelleH There are, that's what I was trying to use with the IFNULLS and NULLIFS. With your perspective I was able to get the correct calculations, pictured below. I also realize I could have done this NULL reassigning in an ETL for simplicity's sake, but I'm just happy it works.

@MichelleH There is something going on with summarizing credits and debits, as credits are all loading in properly but once I attempt to subtract debits I get lots of NaNs. I'm going to check this out and update. EDIT: Total revenue working now, going to try to import into BM. Had to IFNULL both credits and debits…

@MichelleH No I removed it a while ago. There are no other filters applied to this specific card, and the ETL is linear with only a select and group by function.

@MichelleH The calculation validates but leaves me with an "issue occurred during processing" error.

This actually works, but the % difference is far too high. It gives -20%, when it should give closer to -3% or -4%. Almost as if it is taking the difference of only the last recorded days or something, which wouldn't make sense to me if the date filter is working.

Also the final solution should have the following arithmetic, not what I actually posted.

@MichelleH

I believe I figured out the issue, it was in fact the aggregation. When summarizing using real numbers, you have to change the aggregation to an average rather than a sum to get the accurate numbers.

Whoops, hold on, I used an incorrect example. Give me a moment.

Is there no way to do "(2+1)/2" in Domo? This is my main question for future knowledge even if there is another way to solve this problem.

Yes the data is time bound. One is a dataset via email, and the other is a live warehouse. The email dataset records the entirety of revenue for a single date, while the live warehouse is continuously updated. The issue is that due to nonexistent accountability mechanisms in the recording of our datapoints from the emailed…

Yes I understand that, thank you for making sure. I am not trying to reference columns. I want to use manually entered numbers as a sort of "snapshot" instead of referencing specific columns. I have to take the amount of accounts from one dataset and the revenue figures from a different one, without any way to link the two.

Thank you @GrantSmith for the quick reply. The difficulty lies with referencing a column. I have two unlinkable and somewhat dirty datasets that our team is working to correct, but the numbers are all there. I want to get MMR using the following logic (imagine the title says MMR, not Avg revenue per acc): EDIT: I should…

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

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

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.

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,…

Am I intended to replace the input dataset in this with the one I created in the step "Base Date Dimension" or with my report dataset (my guess is the one I created but I don't want to screw up)? Also, I only need last year, so should I be fine to just go ahead and remove whatever dates I don't want or will that break…

See above comment

Hey Grant, thanks for the quick reply. I am a new Domo user, and I am unfamiliar as to where to go to paste the custom code for under the "Magic ETL" section. What exactly is a "Magic ETL palette?"