GrantSmith Coach

Additional Tips Future Dates If you're not interested in having future dates you can filter those out of your initial offset dataset transform table (dates) if you're using MySQL. For example: select d.`dt` from `calendar_dates` d WHERE d.`dt` <= CURRENT_DATE() If you're using Magic ETL you can add an initial filter and…

Chart Filtering To remove the possibility of confusion and ensure your chart is displayed properly I recommend filtering in your card only the period types you're attempting to visualize.

Graphing Your Period over Period The traditional period over period type graphs won't work with this method however there is a really good alternative in the Line+Bar graphs. This will allow you to plot the different period types along with the percentage difference. You just need to do a few best modes depending on what…

Joining Datasets Together Once you have your dataset with the different user defined periods created from above you're ready to join it to your original dataset. You can use any of the options at your disposal (MySQL Dataflow, Magic ETL dataflow, Redshit dataflow, Fusions) however my recommendation are the new Dataset…

Magic ETL This code will work for Magic 1 or Magic 2. Just cope the code in the code block below and then use Command+V (Mac) or Control+V (Windows) to paste the code into your Magic ETL palette. You will need to define the input dataset and the output dataset. {"contentType":"domo/dataflow-actions","data":[{"name":"[DIM]…

MySQL Use the Date Dimension dataset you pulled in under the Base Date Dimension step above as the input to a new MySQL dataflow. Note: I named by input dataset calendar_dates Add a Table transform and call it dates with the following code: select d.`dt` from `calendar_dates` d Put this code into the output datasets:…

Configuring The Periods The next step is to configure the different periods. In this instance it was easiest for me to utilize a MySQL dataflow but the same logical process could be utilized in a MagicETL dataflow as well. I've outlined code to calculate the following periods however it can be expanded using the same…

Base Date Dimension To start we need a list of dates. Domo provides one in their date dimension connector. You can get it installed here (replace [CUSTOMER] with your instance name): https://[CUSTOMER].domo.com/appstore/connectors/com.domo.connector.domodimension?origin=dc The file you'll want to import from the list is…

Dataset Format Overview The resulting dataset will have three different columns: Report Date - This is the date you will use in your charts / graphs Comparison Date - This is the date you will join your dataset to Period Type - This defines the type of the period / offset

Done.

Hi @Domofied You might be able to do this as a calculated field in a Dataset View but because you're on ETL 1.0 you wouldn't be able to use it as an input into your dataflow. It'd have to be your last step before you visualize your data.

Hi @WizardOz I’ve found removing the group by and replying the groupings usually resolves this issue but I would definitely reach out to Domo support to raise this issue with them.

Hi @Rvannoy What is this section of code doing? and (case when right(`CurrentMonth`,2)-0 = '1' then 'Q01' when right(`CurrentMonth`,2)-0 = '2' then 'Q01' when right(`CurrentMonth`,2)-0 = '3' then 'Q01' when right(`CurrentMonth`,2)-0 = '4' then 'Q02' when right(`CurrentMonth`,2)-0 = '5' then 'Q02' when…

Hi @user095063 This is because you already have a dataset with the date listed multiple times. There are a few options to do an upsert using Magic ETL 1, Magic ETL 2.0 and MySQL. Note that as your dataset grows these will take longer to run. Magic ETL 1.0: Creating a Recursive/Snapshot Magic ETL DataFlow Magic ETL 2.0:…

Hi @Domofied If you have Magic ETL 2.0 you can use the regex_replace function to pass in processing flags - this isn't possible with the ETL 1.0 Replace Text tile. By default regex101 as the g (global) and m (multiline) flags enabled whereas Domo does not. Passing in the 'm' as the third parameter should resolve your…

@Ritwik Thinking about this a bit more - you could utilize a Dataset View and a calculated field with a window function to calculate the rolling max and then use that as an input into your Magic 2.0 Dataflow. Just make sure your partitioning correctly based on your assets. MAX(`random_number`) OVER (ORDER BY `dt`)

Hi @Ritwik You can use the LAG function in the rank and window tile to calculate the different columns/values for the past X rows (depending on how long your window is). Assuming you have Magic ETL 2.0 you can then utilize an Add Formula Tile to calculate the GREATEST value of all of your lag columns. Not pretty but that…

@MarkSnodgrass is getting too quick! As he said it's mostly for display purposes but it's also useful for helping to classify the type of dataset it is or where it came from - from a metadata aspect. If you only have SQL server then there really isn't much of a difference but can be helpful if you're using multiple…

Hi @Domofied If you have Magic ETL 2.0 you can use the regex_replace function to pass in processing flags - this isn't possible with the ETL 1.0 Replace Text tile. By default regex101 as the g (global) and m (multiline) flags enabled whereas Domo does not. Passing in the 'm' as the third parameter should resolve your…

Hi @User_32265 Domo provides a connector called Domo Dimensions (https://domohelp.domo.com/hc/en-us/articles/360042931454-Domo-Dimensions-Connector). There's a list of files in there that you can select for some predefined datasets, one being a calendar (calendar.csv). You'll use the connector to pull in that specific…

@Salmas Its a bit difficult to help diagnose your issue without understanding what the current counts are and what you’re expecting them to be. Do you have any sample anonymized data you can post to help paint a better picture of your issue?

I’m going to add my two cents for a date offset dimension table. It will allow you to compare the same amount of days for the current month for the prior period avoiding the potential dip you can see when comparing the current incomplete month. I’ve done a prior write up about this here:…

@WizardOz Have you tried removing all of your groupings and filters and then reapplying them?

If I recall correctly you should be able to pass in the abbreviation of the time zone like EST or the full ISO name like US/Eastern as parameters to CONVERT_TZ

I’d like to see more detailed errors all throughout the Domo product so I can try and diagnose problems easier instead of having to submit support tickets. You could even make it a flag each instance could enable if they wish.

Is there any error message it tells you or is it something generic? Does the job consistently run successfully on the manual retry? Have you tried creating a new connector for this one spreadsheet to see if that resolved the issue? Are there any spaces or other non-alphanumeric characters in your file name?

Are you using workbench or the connector to pull your Postgres data? Typically when I needed more control locally over my pipeline I’ll write a Python script to process my data locally within the script and then upload the resulting dataset via the API/pydomo SDK

You’d need to log into AWS and generate a key and secret to use. The connector doesn’t support SSO authentication

Hi @Dharshini another alternative is window functions which allow you to aggregate across a partition / subset of your entire dataset. You need to have your CSM turn it on in your instance to utilize them in beast modes. SUM(undergraduate_population) / sum(sum(undergraduate_population)) over (partition by act_score) I…

Our of the box it’s not possible to combine both of those charts in the same visualization. As @MarkSnodgrass mention you could use a drill path which requires the user to click into the graph. @amehdad21 mentioned you could do the tooltip but if you want a pie chart on the map chart you’d need to do a custom app. You…