Best Of
Re: Variable Manager (Similar to Beast Mode Manager)
Completely agree that having a way to manage variables would be helpful, @swagner. Expanding Beast Mode Manager to include variables would be a great way to do it since variables require beast modes to function in cards and dashboards.
MdeLeon
Re: Calculated Column Errors - count conditions when like this but not like that
Should work if you remove the then 'Refined' part at the end and move the session_id . See below:
COUNT(DISTINCT CASE WHEN event_name NOT LIKE '%select_item%' and event_name LIKE '%search%' THEN session_id END )
Keep in mind that you are using wildcard characters around select_item and search which will return values that contain characters on either side of that string search.
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
Re: ETL | Alter Columns Tile | 'add select all columns'
Hi Timehat!
That's correct, we are bulk renaming and data type altering for all columns that are coming in from dynamic API customer connector builds. This would allow us to have the ability to add all columns in bulk to then alter names and data types in a more expeditious fashion. Any new fields from the API will pass through as necessary..
Thank you!
KP13
Re: Getting no data in calculation in my beast mode.
You have a lot of parantheses going on so it is a little hard to tell, but I don't think you have a second argument for your NULLIF function.
Additionally, I would recommend evaluating months by using the LAST_DAY() function instead of just the MONTH function. Depending on the date range of your data, you can be looking at multiple years when you don't intend to. Also, for January, MONTH(date)-1 will result in 0, which will be a problem. Doing the following for each, should work better:
LAST_DAY(date)
LAST_DAY(CURDATE())
DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 1 month)
Substituting these into the equivalent spots above should help with better evaluations.
Re: How to calculate against row values, instead of a summation, when using a beast mode within a pivot
@muahmmad_zaidi You can set up your % Change beast mode to calculate based off of the sums of your two fields like this:
(sum(`Field1`) - sum(`Field2`))/sum(`Field2`)
Re: Single Value Card - Value is 0
@MThebeau Can you please share your beast mode code so we can look into it further?
Re: Ways to visualize datasets impact beyond first level
I agree. It would be helpful to be able to see "Direct Impact" vs "Total Impact" as separate columns in the DomoStats/Governance datasets. In our case we could use this information to more quickly prioritize dataset cleanup/migration projects.
Re: Beastmode: Previous 7 Day Avg Comparison
@DashboardDude I appreciate the time and effort you put in here to try to help with my issue, unfortunately, I need the 7 day average to be the 7 days prior to each date, not just the last 7 days. I was able to grab this information using that Lag function in the Rank and Window tile in an ETL which works great for me on my group by functions that are sums of a column, the issue I run into is that some of the beast mode formulas when used in the Group By tile in an ETL are are not effective unless looking at specifically the data that I'm grouping by: Sales Channel, Deal and Date.
The workaround I came to was by duplicating the dataset in the ETL and creating Group By tiles with specific granularity for specific cards: Summary card with all sales channels and deals (Group by Date only), Summary card partitioned by Sales Channel (Group by Date and Sales Channel), Summary Card partitioned by Sales Channel & Deal (Group by Date, Sales Channel and Deal).
I then created a constant called "Granularity" that can be used to filter on each card to select the specific granularity that I wish to view the summary numbers at and then I appended these together:
The avg calculation is then done on a beast mode in the card as the number of days that I'm averaging is based on a variable that's adjusted on the dashboard:
While I wish that I could do this on a Mega Table with the Transpose feature rather than having to create this whole dataflow to achieve the results I need, I just can't figure out how that would be possible.
This is my final result using the ETL:
lbg123