jaeW_at_Onyx Coach

@AnwarBham i think the important thing to keep in mind is that you're not working in a SQL environment. so while it helps to understand how sql works and know where the overlaps are, it's not a 1:1 match. can you update a dataset in ETL without creating a new dataset. yes. add a filter that's always false. should you do…

@AnwarBham you probably shouldn't delete intermediate tables and dataflows. It'll be impossible to reconstruct your pipeline without rebuilding the dataflows all over again. 1) you could refactor and consolidate your intermediate steps 2) do nothing. if row limits are not a problem, it doesn't really matter. bigger…

or a window function in data set views.

If you're using this to adjust for closing activities in a financial use case, you should just create a table of months and when the month was closed and work that into your data. Just blanket filtering to exclude the current month is ... a solution that may lead to heartache down t he road.

@DuncanDomo kindly recreated his solution here: https://domo-dojo.domo.com/datacenter/dataflows/44/author

@ChristianW , a Bullet Chart would work but there's also a Bar and Line chart type too.

Both @MarkSnodgrass and @Giacomo are correct, you cannot nest Count Unique inside a Window Function. ... well.... you can, but the answer will be 1. What's your use case? What question are you trying to answer?

Domo HTML Charts will display content that's been encoded with HTML tags. Mega Charts support some HTML encoding. I believe Pivot Tables support the least amount of HTML encoding.

@msharma , @rahul93 is partially correct, if you put a '' in your ELSE clause you will convert your beastmode into a string. It therefore wouldn't format or behave like a number after that point. probably not desireable. To get 'nothing' as Rahul is recommending, you can however use ELSE NULL sum(case when…

@mberkeley pretty sure you can get the result without pre-aggregating your data. @GrantSmith and I both proposed solutions that should work here: https://dojo.domo.com/discussion/52390/tableau-like-parameter-actions#latest

congrats @DuncanDomo , also thank you for doing this write-up. that's phenomenal and a real boon to the rest of the community next time they need to build a stored proc. We're building a repository of dojo solutions in domo-dojo.domo.com if you're not part of the instance, I'd be happy to add you so we can put your ETL…

@gbuckley , you could select *, fruit as ReportFruit From table UNION select * 'all fruit' as ReportFruit from table then put ReportFruit as the Filter, you have to always include the 'all fruit' as well as the desired fruit. then sum(case when ReportFruit <> 'all Fruit' then amount) / sum(case when ReportFruit = 'all…

You can do it in magic but it would be a little sloppy. MySQL might be easier if you've got the SQL chops. I would have done a FULL OUTER of JOIN of both date tables. then coaleasce dates from a and dates from B. then if there is no value in table b use the lag() or lead() function to find the nearest value from table A…

Duncan "generate output table" is a feature that may have been deprecated since the KB was written. I've never seen it. I usually create an output table where i SELECT * <tableCreatedInStoredProc>

add a column to your transactional data, n_th sale, using a rank & window function. you can use Row_Number or Rank depending on the granularity of the data. Now just alter your beast mode. avg( case when n_th sale = 1 then DATEDIFF(`sale_date`,`signup_date`) end )

@danidiaz what you're trying to do, AFAIK cannot be done without a Window Function and RANK or ROW_Number to get your "other" category, you'll have to caluclate the rank inside of a Dataset View and then build a CASE statement on top of the RANK column. Here's a bit of a long video but somewhat comprehensive. Also, ask…

ok... so... there's no such thing as 'Domo MySQL'. It's just MySQL 5.6 if it works in MySQL 5.6 in a local install it should work in the ETL. Domo does limit access to a few system tables but it's the same. There's an example here https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Dynamic_Pivot

@Jobur you totally can create a webform from a dataflow, you just have to do a little hacking. I covered it at the IDEAs exchange conference: https://www.youtube.com/watch?v=YgJLijj7vmw&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=18&t=3s

@ST_-Superman-_ we would have to add the user to domo-dojo. @user046467 if you haven't been added yet please email me your name and email. jae@onyxreporting.com that said, i try to avoid pre-aggregating data as Superman has done in ETL because it your card unable to respond to filters because the values are hard-coded.…

The error looks like a limit on the API. Your screenshot from Salesforce literally reads "we can show up to 2000 rows" ... Try choosing a different report.

@mattwelykholowa I wouldn't hold my breath... this isn't exactly a feature that would be oft requested. What you could do is either 1) try to hack the ETL using the Domo CLI to alter the dataset or alter the dataflow like i demonstrate in this video. or 2) just use a script + export-dataset command in the CLI to just…

@faisalnjit it's not great. Domo did not design dev ops into their product. you can manage alerts for users using the CLI. If it were me, i would use a DSV (dataset view) as a semantic layer between the data and the cards. The DSV would never change, but you could swap in new datasets as necessary. Alternatively, you can…

@mhansell , @GrantSmith there is a feature to keep roling windows. Looks like it's a premium feature. but talk to your CSM for details. Additionally there's a command in the JavaCLI. When I'm consulting with clients, we work over the AE / CSM appealing to their desire for you to sign the renewal / improve adoption. We use…

... what's the error?

@imelendez :P you posted two threads about the same thing! ok. extend your timezone_id table to include a column "offset_from_utc" when not under the impact of daylight savings. then build a date dimension with the binary isDST. then you can do the adjustments i described in the other post.

I know this issue is already resolved but you should try building this in Magic. It will be SIGNIFICANTLY faster and be much easier to maintain because what you descibed can be done with two tiles. RANK (rownumber) and Filter where Rank = 1 1) from the end user perspective, one of the nice things about Domo is that it…

@user19019 , any chance you could leave a little writeup in the Dojo on what the resolution was? it'll be great for future visitors to the Dojo.

I stand by my previous recommendation @user048760 instead of having one row per site with KPIs in columns i would recommend the schema Site, Date, KPI Name, KPI value This will be a much more flexible model that won't force you to pre-aggregate your data and allow you to build more extensible models at different…

Here's a link to Grant's entire presentation!

@MarkSnodgrass is correct there is no referential way to refer to pre-defined beast modes. @JustinB you generally should not nest aggregations inside a CASE statement. case when (sum(`Clicks`) / sum(`Impressions`)) < x then 0 when (sum(`Clicks`) / sum(`Impressions`)) < y then 1 when (sum(`Clicks`) / sum(`Impressions`)) < z…