GrantSmith Coach

Hi @JunkDoom Your configuration would work to assure you don’t have duplicate records. If you’re wanting to make sure you don’t pull in any record that already exist I recommend looking into the lastvalue replacement variable in workbench query. Workbench will track the last value it sees each run and you can add a…

Hi @etszyingho Your beast mode should automatically take into account your date filtering. Since you're using a pivot table things get a bit funky. How are you partitioning your data within your pivot table (what are you using for the columns)?

Have you tried URL encoding your pfilters since you're using IFrames? You can use a website like https://www.urlencoder.org to easily convert it for you.

Alternatively you could utilize some math functions to simplify your beast mode to calculate just the tenure in weeks - this would allow easier filtering using a single numeric value: FLOOR(DATEDIFF(CURRENT_DATE(), `dt`) / 7)

I don't believe there's a way to have the best of both worlds. It's either wrap text to fit everything or have a scroll bar. That might be a good idea for the idea exchange.

Hi @User_32265 You can freeze a specific number of columns on the left side of the mega table. Under General there's an option for "Number of Locked Columns" to input the number of columns to lock/freeze. When columns are locked, they always appear in the table, even when scrolling to the right.…

The multi-line and single-line uses the same UI. It can be a bit confusing. Good luck with your calculations.

Hi @User_32612 A regression line isn't possible in a multi-line graph as Domo doesn't know which data points to perform the regression analysis on. You can only do it on a single line chart. You may attempt to calculate those metrics manually for each individual series or across your entire dataset but out of the box it's…

As Mark said you’re not able to share filters currently. You could look into deep linking with pfilter parameters and sharing a URL https://domohelp.domo.com/hc/en-us/articles/360042933114-Using-Pfilters-to-Apply-Filters-from-URL-Query-Parameters-to-Embedded-Dashboards

To simplify your approach I'd recommend using a date offset dimension dataset and utilizing that to calculate period over period calculations. I did a whole writeup of it here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest If you would like to continue down your…

PDP is the way to go when limiting access to the different records in your dataset however PDP can’t hide a specific column. I’d recommend a dataset view to not select the columns with PDP to restrict access to solve your issues

Conclusions I hope you enjoyed this walk through and found it useful. Feel free to post any questions or clarifications or even your own tips / tricks!

Caveats Dates are a tricky thing. If you subtract a month from March 31st you'll get February 28th (or 29th depending on the year... again, dates are tricky) so there is a chance that if you're comparing month over month you might count a day multiple times (March 28->February 28, 29->28, 30->28, 31->28) so it may not be…

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…