jaeW_at_Onyx Coach

@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…

@imelendez Nihar did a whole session about this at the recent conference. https://www.youtube.com/watch?v=kmC-0ncu9PA Let's unpack a few things. 1) why do you care if it takes 15 minutes to run a dataflow? how often are you trying to update the data? you can't use a recursive dataflow AND have low latency run times. the…

you probably shouldn't delete the dataflow. if you ever want to update that output dataset again, it will be a non-trivial challenge to push new data into it. if it were me i'd leave all the content there. if you MUST get rid of input datasets to save on row count then just delete the inputs. there's no cost to having…

given how new and novel the connector is, i would recommend opening a ticket with support. are you able to work around it by sending data via postman or Zapier?

@user048760 , why do you need to aggregate rows in ETL? If you just put SiteID on an axis in a card and then take the sum of those columns it would do the same thing without the extra step. Is there more to your use case? (where i'm going with this is generally you want to avoid aggregating your data in ETL).

@SeanPT thaaaaats gonna get expensive. do you want to post a screenshot of what you've currently got and describe the JOIN?

I believe Domo broke this functionality back in Feb of this year and never fixed it. you can't do math on the PARTITION BY clause on Dates and then aggregate to the week using the Date filter grouping functionality. Instead, calculate YearWeek as a materialized column on your dataset and change your Partition By Clause to…

@daniellecroft contact your CSM or support@domo.com. the feature you're asking for is "filter on aggregates in analyzer"

what's your business requirement. i think most people would say you can't take the average of an average. you can take the max of an average. If i had to guess you have a bunch of transactions. you want the daily average. so you put date on the X Axis and then Avg(amount) That works fine. now you want the max of the daily…

@mattAnonomatic ... is it fair to assume that you can connect to a domo instance? also are you able to use an older (not beta) version of Workbench successfully? does workbench crash with other job types? (odbc)