Best Of
Re: At what stage should column renaming happen
I agree with both @Ashleigh and @GrantSmith . I prefer to change column names within the ETL. As a best practice, I will also then make a note on the output dataset to state the source and original name of that field by utilizing the Tags and Description fields when viewing the dataset
Re: case when not blank
The parentheses were in the wrong place, in this particular case there is no need for parentheses. Yes you would just filter to greater than 0 in this case since your options are just 0 and 1. You can make your 1 and 0 anything you would like, if you would rather use 'true' and 'false' you could do that too, whichever makes more sense to you.
If you are doing a grouped by on the bar you will want to create another beastmode and use it in the grouped by. Something like ...
case
when sfuser1 is not null then 'sfuser1'
when sfuser2 is not null then 'sfuser2'
when sfuser3 is not null then 'sfuser3'
end
This will bucket them first in sfuser1 then sfuser2 then sfuser3
Re: How to convert Milliseconds into Date
Hi @Vova
Try this, you might need to adjust the time depending of your time zone
FROM_UNIXTIME(1656076776827/1000,'%d %M %Y %H:%i:%s')
Godiepi
June 2022 User Group Recordings
Rewatch the presentations from the June Virtual User Group. If you'd like access to public data files, see this post on how to join the Dojo Domo instance.
Dan Brinton, Senior Technical Success Manager at Domo: Automating Data Validation using Alerts
Mark Snodgrass, Director of Data Strategy at NLC Mutual: Custom Charts
Jace Mclean, Director of Data Insights at Domo: Variables and Fixed Functions
Elliot Leonard, Salesforce Developer at Armis: Domo Slack Integration
Huge thank you to our amazing speakers for this event!
Re: Is there a way to count totals of unique combos within a pivot?
@slesty If you want to see the the totals by shipment, then I recommend creating a separate output to your ETL to remove all customer-related fields and remove duplicates so have a single row per shipment. Then you would use that output for cards to aggregate the invoice amounts without double-counting, and use your original dataset for customer-specific reporting.
Re: Beast Mode calc for omitting specific modalities?
If you wanted to go the beast mode route, you're on the right track. You need to create a CASE WHEN statement within the SUM function:
SUM(case when 'Divisional Expenses' <> "Marketing" then 'Amount' else 0 end)
'Amount' would be whatever your value column is. Another option is to just do a regular sum of you Divisional Expenses column and just add a Divisional Expenses as a filter to your card and you can exclude whatever modalities you want. Then they won't be added and you can easily change the filter depending on what you want to exclude instead of having to go and edit the beast mode each time.
Re: Become a Domo Sensei
@Ashleigh, keeping status is a yearly thing. So annually, you will speak at one event (user group, DP, etc.) and write two blog articles. The perks are yearly as well. Let me know if you have any more questions.
Re: Beast Mode Help Avg Order Value PoP
Instead of using count for your orders you need to sum them instead.
Re: Getting dates in one row
There are two separate pieces needed to pull this off in Magic ETL.
- Group By Order, Model, Ord Date - get MAX of cur prm and each of the prm date fields
- Pivot the rec dates (New Magic ETL Tiles: Pivot – Domo)
Left join the Pivot to Group By on Order, Model, Ord Date - drop the extra columns of the same name from Pivot.
kdeike
Re: Finding average of difference in two different dates
Since you're doing a windowing function have you looked into the Rank & Window tile?