jaeW_at_Onyx Coach

@Arjuman in addition to posting raw code, it might be beneficial to describe what you're trying to accomplish and let the community know what shape your table is in. it'll certainly help in cases when your SQL is bugged and you're looking for support trying to make it work. (CASE when YEAR(`ResolvedDate`) =…

@LCash i wouldn't recommend it, but you could put your product actuals into separate columns using PIVOT in Magic 2.0 it may seem counter intuitive but stacking the data (date, category, metric) would actually be the best solution , because if you had data that was not preaggregated (if you had multiple product x sales in…

in the kindest way possible ... if you are having difficulties writing SQL maybe you should consider implementing your dataflow as Magic 2.0 which doesn't require you to code in SQL. select distinct Customer from( select Customer, count(distinct SalesOrder) as Purchases from ArTrnDetail where InvoiceDate between…

@Dr_Duru see roles. https://domohelp.domo.com/hc/en-us/articles/360043438953-Default-Security-Role-Reference#:~:text=The%20various%20default%20security%20roles,security%20roles%20available%20in%20Domo.

@TomKelleher Full authentication: https://www.youtube.com/watch?v=d2WAKIKpKlE&t=1s https://www.youtube.com/watch?v=hRwrZABP8RE&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=14

Just use a pivot table card and put YearMonth (might need a beast mode) on the Columns axis and Locations on the X axis and make your metric the count https://domohelp.domo.com/hc/en-us/articles/360043429473-Pivot-Table

@Joe_M it sounds like you're describing ... a normal filter card. https://domohelp.domo.com/hc/en-us/articles/360043429313-Slicer-Checkbox-and-Radio-Button-Cards

seems like this feature exists: "If you have a "Participant" default security role, you cannot share filter views or set default filter views for the page, but you can add and save filter views for personal use. Participants can also select a filter shared with them, make changes to the filter and save it as a new filter…

@AJ2020 this is an interesting question. are you asking for max columns you can read in via a connector? or maximum columns you can create in ETL? I believe the max columns is 1024 but i'm probably wrong. More importantly... why are you hitting the max number of columns? Could you shape the data in a way that's more…

@Jack_Kaye to be clear you're trying to query Domo, NOT query an on prem database? i'm sure it can be done but I'm not sure you're gonna get the desired end-user experience... Adrenaline wasn't really designed like Snowflake or TSQL in a cloud environment where you should be able to query the database with an IDE. The ODBC…

instead of materializing this using a bunch of CASE statements consider just building a pivot table card in Analyzer.

indexing IS very dataset specific. and you're using MySQL 5.6 so all the updates in the platform in the last ... 10? years are not in place. so yeah... a DBA is your best bet if you insist on optimizing your query performance by hand. PS in MySQL dataflows you can use the 'explain sql' button (it's a drop down under "run…

@MarkSnodgrass and @ST_-Superman-_ have super creative ideas... but i strongly recommend calculating this in ETL... your card won't respond to page filters and if users aren't using filters then you're probablly creating single use datasets which isn't ... ideal. consider building a table card with a LIMIT=3 (down in the…

instead of adding columns, consider adding rows. consider trying to create an 'accruing_schedule' dataset that says accrument_date, original_receipt date, po_id, po_id_accrument no, PO_amount, accrument_amt so if your PO_amount was 10,000 and it was to be accrued over 4 months you'd duplicate the row 4 times, you'd…

@Chris_Wolman that's a shame. you can however use the Java-CLI to push your own partition tags. so if you must partition on non-date columns, the CLI may be your best choice.

@mhouston you probably can't automate allocation. you could try building buisness logic. so like if the amount posted is negative then just subtract it from previous entries (so we operate on the assumption that we aren't back posting two or three week ago adjustments). this might help you smooth out the negative spikes…

Just keep in mind NULL =/= 0 avg(5 , null, 3) = 4 avg( 5, 0 ,3) = 8/3 wrapping your initial columns in IFNULL( x, 0 ) will skew any average calcs you run on your dataset moving forward. also, i strongly recommend against preaggregating your datasets in ETL, this might lead to single use datasets that have limited…

@MarkSnodgrass i think this is logically flawed. if you calculate the number of distinct customers in a month, you cannot add each month's number for a year. i.e. 50 in jan, 35 in feb = 85 distinct customers. you would be assuming that you had no repeat customers... if your data is measured per month and you want a rolling…

@MarkSnodgrass careful! you are making the assumption that FILTER ROWS only contains one row per Engagement ID where filter is true. if you are incorrect you would be causing row growth in the JOIN. if you want the FIRST or LAST instance of filter is true, then you should add a RANK & WINDOW tile after the filter.…

@charles there isn't a preconfigured tile for it, but if you define what "market basket analysis" is, you can recreate those steps in Domo. alternatively domo does have the jupyter notebook integration, you could use python packages for your DS needs.

+1 @MarkSnodgrass Yes you can duplicate connectors using APIs, but getting the syntax correct is such a hassle. Unless you have tens of connectors, just going through the process would probably be most time efficient.

@Matt_Umolac93 typically for trouble shooting window functions you should separate components into separate columns. i.e. put the numerator in one column and the denominator in another. this way you can isolate the culprit. are you confident your CASE statement is working as expected? if you write sum(sum(amount)) over ()…

there is an API for sharing. if you share a page with a user all the cards are shared automatically, but i believe domo changed functionality a few years ago so all the datasets aren't shared automatically. i may be mistaken (@GrantSmith ?) but if you share datasets with a group, all the users would receive access at once.…

@mberkeley the SQL driver is built by Netsuite. Netsuite would have to build that functionality into the driver before Domo's connector would support it. Also purely from a best practices point of view it might make sense to import the data as is. yes it does lead to higher data volumes, but in Domo for filtering purposes…

looks like you could split your data using a comma delimiter. have a look at the split_part() function in MagicETL > Formula tile from there you can use the same techniques laid out in this video, https://www.youtube.com/watch?v=oYcpYE7DiV4. if you need to start on just the content between the square brackets [] then start…

@jrtomici what is a "header row". if it's a column that labels or identifies a row, in traditional database nomenclature we'd just call that the row_id or primary_key. if that's the case, then there's no reason to import as a crosstab. just import it as a normal dataset. then you can use filters to choose which rows to…

@pstrauss splitting multiple multi-value columns into rows would cause massive row explosion. imagine one row with 3 values in column a and 3 values in column b. you end up with 9 rows. if you do that across multiple columns, huge row growth. instead, consider a dimensional approach. take each of your multivalue columns in…

uh... would it be fair to say you want to rank orders by date ascending, and just want to know the date_diff of the first order and today? do your rank() function in ETL and then do your datediff in Beast Mode.

remember zero and null are not the same. assuming your beast mode is sum(values) / count(values) if you had 5 rows 0,0, null, 2 and 6, the average is 8/4 = 2 if you had 5 rows null, null, null, 2 and 6 the average is 8 / 2 = 4. if you are doing division within the row avg( colA / colB ) as opposed to sum(colA) / sum(colB)…

what's your beast mode?