Best Of
Re: Beast Mode Calculation
Hi @Shumilex
I'd recommend utilize a custom date offset where you can compare one date to a different date. In your case you're just wanting to know the value from last week. I've done a write up / walk through of this methodology before. You can find more information here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest
You'd then just need to calculate your percentage difference:
(SUM(CASE WHEN `Period Type` = 'Current' THEN `Value` END) - SUM(CASE WHEN `Period Type` = 'Last Week' THEN `Value` END)) / WHEN `Period Type` = 'Last Week' THEN `Value` END)
Re: Beast Mode Calculation
To address the 4 week rolling average you could utilize the LAG window function however you'd need to have 28 different LAG functions (I'm assuming your date has a day grain and not a week grain here. If you have it by week this becomes easier and you can refer to the post @MarkSnodgrass mentioned) called in your beast mode which is a bit much to write. What I might also recommend is to have a 2 weeks ago, 3 weeks ago and 4 weeks ago offset you define and then just tweak your PoP beast mode (this is assuming you want weeks 0-3 vs 1-4 if not you could apply the same method for weeks 5-8):
(SUM(CASE WHEN `Period Type` IN ('Current', 'Last Week', '2 Weeks Ago', '3 Weeks Ago') THEN `Value` END)
-
SUM(CASE WHEN `Period Type` IN ('Last Week', '2 Weeks Ago', '3 Weeks Ago', '4 Weeks Ago') THEN `Value` END))
/
WHEN `Period Type` IN ('Last Week', '2 Weeks Ago', '3 Weeks Ago', '4 Weeks Ago') THEN `Value` END)
Re: Assembler Upsert Date Column
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: Creating a Recursive/Snapshot DataFlow in Magic ETL v2 (Beta)
MySQL: Creating a Recursive/Snapshot SQL DataFlow
If you don't care about potential duplicates you could configure the Dataset Copy connector and set it to append and have it run on a daily basis then use a dataset view to append your historical and now live/historical-ish dataset if you can't recreate your historical dataset. (You may be able to configure the connector to first pull the historical dataset then change it to pull the daily dataset instead on a daily basis)
Re: Pydomo creating dataset arguments
Hi @DKCloud9
You’ll define the schema of your data set with a DataSetRequest object and pass that into when you create your dataset. There is an example of how this works on the GitHub repository specifically lines 14-17 (creation) and 33-38 (updating)
Re: Problem with SQL for WordCloud Chart
You can add another SQL transformation (not a table one) to have it drop the final table if it exists before you run the word_cloud function call because in the MySQL database that's spun up it'll create the table but won't ever remove it when you attempt to create it again and cause the table already existing error.
DROP TABLE IF EXISTS `final`;
This will allow you to recreate the table as many times as you need during your testing.
You may need to reach out to support to see if they can give you a better error message from the execution.
Re: Reg. Expression to replace a list of names with a single character.
@GrantSmith @MarkSnodgrass @jaeW_at_Onyx You guys are awesome. I appreciate all the insight!
Re: Design options
Hi @Chris_Hippo
I typically will use Text cards instead of Headers on dashboards as they give you much more options in terms of text color, size and formatting however changing the font face currently isn't an option.
Re: Group by Month, sorting by Year with that month
Thanks again for both of your help, as here is the end result. I learned how and what a trellis does, as I hadn't had a need before.
Re: Group by Month, sorting by Year with that month
@MarkSnodgrass Able to finally get it to sort correctly using MONTH(`Event_Dt`) + (YEAR(`Event_Dt`)/10000). Really not sure why the other ways I was doing wasn't working. @GrantSmith Not aware of that feature, will test now
Re: Division by Zero ETL error
Hi @Emma
You need to check for your denominator to be zero, not your numerator. Try this (will return 0 if `Conservative Max Qty per plt` is 0)
CASE WHEN `Conservative Max Qty per plt` = 0 THEN 0 ELSE `Total Stock`/`Conservative Max Qty per plt` END