GrantSmith Coach

Hi @user016095 This is a common question regarding looking back in time. The best way to think about this is to restructure your data. With your beast mode you're asking essentially is 1 = 1 - 364 which will never be true because beast modes compare data within the same row. What you can do is join your data to itself but…

Hi @Chris_Wolman With WB5 they took away the more detailed scheduling in favor of the "sometime in this hour" schedule. If you really needed it to you could utilize Windows Scheduler to have a more fine tuned schedule which would then call the wb.exe executable telling it to run the job at the specific time you desire.

Hi @user075492 Domo has some governance datasets which would help with your analysis. Specifically the DomoStats connector contains an Activity Log dataset which will show you all the different actions a user has taken including card views, page views etc.

Hi @user069636 You can calculate the first day in a week using the DAYOFWEEK function in your beast mode. SUM(CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY)AND (`Date`) <= CURRENT_DATE()THEN `Units`END) DAYOFWEEK returns values between 1 (Sunday) and 7 (Saturday). Subtracting that…

Alternatively yes, you can just filter for last month in the date selector which is probably the cleanest ?

Hi @Ashleigh There's several ways to tackle this. One possible way is to utilize a date dimension to calculate last month data. This has been outlined before on the Dojo. Alternatively the quickest option would be to utilize the LAST_DAY function in your beast mode. CASE WHEN LAST_DAY(`dt`) = LAST_DAY(CURRENT_DATE()) THEN…

Hi @Shumilex You'll want to utilize a windowing function to calculate the sum for each partition / typename and then divide that by the sum of the entire dataset. SUM(SUM(1)) OVER (PARTITION BY `typename`) / SUM(SUM(1)) OVER () If you don't have windowing functions enabled you'll need to talk with your CSM to get that…

Hi @user033540 You likely have a difference of 4 hours because Domo stores dates and timestamps as UTC under the hood and then converts the dates when displaying the data in a card. You need to make sure your data is in UTC time when ingesting it into Domo. You can utilize a Timestamp transform on a workbench job to…

Hi @user004289 If you're referring to the preview within Magic ETL it's limited to 100 rows. If you want to see more you'll need to output it to a dataset to review the entire dataset contents.

GROUP_CONCAT is the MySQL version of LISTAGG group_concat(MyString separator ', ')

Hi @user031930 Are you using public or private embed? Only with private embed can you apply and change filters. Here's the knowledge base article describing some limitations: https://knowledge.domo.com/Engage/Sharing_Content_Outside_of_Domo/Sharing_Cards_and_Dashboards_Outside_of_Domo_Using_Domo_Embed As for page fitlers…

Hi @kboudrie You could attempt to add a Beast Mode as a column which use the same logic as your color rules and returned a description of that specific coloring. If you didn't want it on the same card you could create a new table card and just list that new beastmode as a column and add the same color rules to it so that…

Hi @HashBrowns Domo doesn't like it when you try and take an aggregate (count) of another aggregate (row_number). There's some caveats to this with window functions but how you're utilizing this is why. What I'd recommend you do is create a new Magic ETL (or a Dataset View if you have the beta) to perform your windowing…

Hi @user046467 You could spin up a magic ETL and attempt to convert the data type to a date (using Alter Columns tile in Magic 2.0 or Data Type(?) in 1.0). When you run the preview it'll state which value it wasn't able to convert to a date. Alternatively you could utilize a replace text field with a regular expression to…

Hi @tstimple I've written up a similar solution utilizing UNIX_TIMESTAMP, some simple math and the CONCAT function. I'd recommend taking a look at https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Date-Calculations-minutes-and-seconds/m-p/49259/highlight/true#M8465. That will give you a way to customize your display of the…

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…

In your Beast Mode you need to wrap your SUM() in another SUM() because of how Domo interprets the function. SUM(SUM(`Users`)) over (order by `Section Master`) Here's a good video which describes this in more detail. https://youtu.be/eifSYZIcPzg?t=1055 

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…

 @user052846 I'd recommend you refer to https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Recreate-2-Excel-Formulas-in-both-Magic-ETL-and-Beastmode/m-p/51726/highlight/true#M9183 since your question was asked there.

Hi @user052846 I'd refer to https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Recreate-2-Excel-Formulas-in-both-Magic-ETL-and-Beastmode/m-p/51726/highlight/true#M9183 to help get your question answered since you asked it there as well.

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

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:

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.

Hi @tstimple You can use DATE_DIFF or some math with UNIX_TIMESTAMP (returns the number of seconds as an integer since 1970-01-01 which I prefer as I've had some odd results with date diff.) It appears you've got a single data column Assuming you're doing this in a beast mode it'd look something like this:…

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

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…

SUM works on a row by row basis and only takes a single parameter - the column which holds the value or a calculated value you're wishing to add together across all the rows. If you're wanting the grand total of all 8 columns add them first before your SUM like this: SUM(`Logo` + `Social Media` + `Earned Media` +…

Hi @user068414 Do you have the code for your beast mode? That will help debug your issue.

Any time you have an aggregate function (in your case SUM) that is aggregating a record with a NULL value the entire result ends up being NULL. In this case the ELSE 0 is essentially saying instead of returning NULL (by default if there is no ELSE clause) explicitly return 0 so that the SUM will calculate the correct…

So since you have multiple conditions based on the same date range you'll want to utilize a nested CASE statement like you were originally doing. This will help simplify your beast mode. Case statements follow the following format: CASE WHEN (conditions) THEN Do something WHEN (another condition) THEN Do something…