GrantSmith Coach

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…

Hi @user003454 You'd need to format your data differently to get something like this to work such that a single column would have your categories for Region and Segment combines along with another column denoting the type of category label it is ('Region' or 'Segment'). You could pull only the Region data first and then…

Hi @user062862 Do you have a lot of DOMO.log statements or a large loop logging the same statement over and over? Have you tried commenting out some of those DOMO.log lines to see if that solves your problem? Essentially your error is stating your sending too many DOMO.log messages to the console output. 

Hi @user046467 Without seeing the actual error I'm somewhat guessing here but it appears you're missing your final END for your outer case statement. Depending on how you want your SUM to function currently it'll return NULL if your date is not between 1 and 15 days ago. If you want to count those as 0 then you'd need to…

Hi @user052846 You didn't clearly state what exactly your issue was with your beast mode so I'm somewhat guessing here but you're essentially wanting to use the most recent date between publish date and update date for the publish date if the publish date is null correct? If that's correct then you have two independent…

Hi @hamza_123 Are all of your Timezone values valid? Do you have any null / empty values? Do you have any null or empty timestamp values?

 Hi @Jessemauser Mine continues to function properly. Have you tried recreating a new governance dataset from scratch to see if it's still a problem?

Hi @swagner You can use a Rank & Window Tile. Using SUM on your number field with an unbounded for the first setting and 0 for the last setting. Order by your date field and now partitions will get you a rolling sum / running total.

Hi @user10540 This is simple enough with a window function in a beast mode. From looking at your spreadsheet you're essentially looking to see what % of the maximum number within your date range is. This is a great case for window functions. This is a feature switch so if you don't currently have it you'll need to talk to…

Hi @AWieczorkiewicz Have you looked at the schema tab on the script tile? Does it appear after you run a preview? https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/ETL_Actions%3A_Scripting#Populating_The_Resulting_Schema

I’m on mobile and the dojo won’t let me edit my reply but the formula should look like this for prior CASE WHEN `Column B` = YEAR(CURRENT_DATE) - 1 THEN `Column A` END and this for current CASE WHEN `Column B` = YEAR(CURRENT_DATE) THEN `Column A` END

Hi @user052846 CASE statements are your friend in this case. Assuming you’re looking for data from the prior year you can use logic to conditionally set a new column value using a case statement in a beast mode. For your current Column: CASE WHEN `Column B` YEAR(CURRENT_DATE) THEN `Column A` END Previous Column CASE WHEN…

Hi @user037924 On your date filter card (while editing your dashboard) > Change Interation - do you have Standard interaction type selected with Enable interaction filters checked and the Apply to selected cards radio button selected and only the cards you want affected by the filter selected in the card list? How is your…

Hi @user037924 You can't dynamically change chart properties based on your data. If you'd want to do something like that you might be able to write a custom app to handle this specific scenario but based on how Domo works it's currently not possible.

Hi @user048980 Which SharePoint connector are you utilizing? There's several out there. What's the error message you're getting back from the connector?

You have a minor typo in your formula, it should be DAY not DAYS DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) You can also shorten it since it defaults to DAY intervals: DATE_SUB(CURRENT_DATE, 1)​