Recreate 2 Excel Formulas in both Magic ETL and Beastmode

I am trying to replicate the following formula(s) in excel in DOMO. I hav ebeen using Magic ETL but apparently the mysql library is very limited.

I've provided an example of my dataset that I want to replicate in DOMO using magic etl

Then, if possible, I also want these same formulas replicated in Beastmode.

Formulas in Excel include:

Contribution: =SUM(A$2:A2)/SUM(A:A)

Effort = =F2/MAX(F:F)

Thanks so much for your help.

I've gotten through some of it, but I'm pretty confident there's a less complicated means of doing it out there.

example_test.xlsx

Find more posts tagged with

Comments

GrantSmith

Hi @user052846

Windowed functions are your friend in a beast mode. You'll need to talk to your CSM if you don't have them turned on in your instance. It's a simple feature switch.

Contribution:

SUM(SUM(`Users`)) OVER (ORDER BY `Users` DESC)
/
SUM(SUM(`Users`)) OVER ()

This is calculating a running total of the amount of users in descending order and dividing it by the total of the entire dataset users count.

Rank:

SUM(SUM(1)) OVER (ORDER BY `Users` DESC)

Simply a ranking calculation by adding 1 for each row based on the number of users in descending order

Effort:

SUM(SUM(1)) OVER (ORDER BY `Users` DESC)
/
SUM(SUM(1)) OVER ()

Similar to the contribution except this is taking the rank and dividing it by the total number of rows (in other words the max rank)

leeloo_dallas

Is there a way to do this using magic etl as well?

I don't think "OVER" is supported in magic etl.

GrantSmith

You'd need to use a rank and window tile to do your windowing

leeloo_dallas

I have windowing, but how do I do that in magic etl?

I'm new so understanding how to use it isn't my strong suit yet.

Also, in beastmode, the contribution column is incorrect and doesn't match up with what is displayed in excel.

leeloo_dallas

I also noticed that the Rank & Windowing function does not allow me to put in a formula, it provides me with a drop-down list. Unfortunately, I'm not following how the Rank & Window function helps in this situation. I already have Rank in my magic ETL.

GrantSmith

How is the contribution different? Do you have a specific example? A specific row?

The examples above were for a Beast Mode not for the Magic ETL. I'm working on a magic ETL example for you but will take a bit of time.

leeloo_dallas

Thanks for helping me with this: I've attached a screenshot of the side-by-side results.

Left (domo) Right (Excel)

Capture.PNG

GrantSmith

Are the other fields the same values? Do you have additional columns you're including? Are you doing any other sorting? Are you doing any partitioning in your data?

When I look at your example file and the beast mode they line up:

Screen Shot 2020-12-15 at 3.12.02 PM.png

GrantSmith

To follow up regarding a Magic ETL solution I'm going to try something new and see if this will work. I've attached the JSON representation of some ETL tiles. If you copy the contents of the file and then selecting the canvas of a Magic ETL data flow then paste this in it should put the tiles in the correct configuration. Let me know if it doesn't work and I can upload some screen shots.

You'll need to update the input and output dataset tiles after pasting them in as they won't exist in your instance.

user052846.txt

leeloo_dallas

Hey, so I'm not performing any sorts or filtering on this dataset, it started off all clean.

leeloo_dallas

Actually, I believe they sorted by User first now that I'm looking at it.

That changes a lot of things.

leeloo_dallas

I got the flow to work but the answers still are not the same as the table.

I believe the table had originally been sorted by amount of users which occurs after you apply a rank anyways, but for some reason I don'/t think this can be done in DOMO.

GrantSmith

Have you tried creating another beast mode to calculate the total number of users and the running total of users and comparing that to your numbers? Can you split out your numerator and denominator in your excel document to get the total users and cumulative total to compare?

Total Users

SUM(SUM(`Users`)) OVER ()

Running Total of Users

SUM(SUM(`Users`)) OVER (ORDER BY `Users` DESC)

leeloo_dallas

Are you comparing the results to the excel file I provided?

Have you not looked at the formulas in the excel file I provided?

This question is still not answered, is there someone else who could help?

GrantSmith

As I showed with https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Recreate-2-Excel-Formulas-in-both-Magic-ETL-and-Beastmode/m-p/51741/highlight/true#M9196 I examined your excel file and the formulas, uploaded it into Domo, wrote and compared the beast modes (BM* fields in the screen shot) and they matched.

The Dojo is a community of Domo users / customers who volunteer time out of their own schedules to assist other users. We are not employed by Domo (unless you see a Domo tag on the user icon). Seeing as this appears to be an urgent issue (as you've asked some variation of this question 5 times) and haven't gotten the answer you desire I'd recommend you contact your CSM to have Domo support to directly assist you with your issue.

leeloo_dallas

I've used the dataflow and the beast modes you suggested and neither replicated what you displayed.

So I don't know how the dataflow you gave me is incorrect on my table but not incorrect in yours.

Effort is based off a column but you calculated both Contribution and Effort separately.

I reposted this multiple times because every time I sought help from other users, you provide a link insinuating that the question is answered.

leeloo_dallas

Until you can prove that you applied your beast modes and the dataflow in DOMO and they were identical to what's in excel with screenshots that you didn't use my original contribution and effort columns, it's really not okay to be replying to other posts insinuating that this was answered instead of allowing me to get a second opinion. That's just not cool.