jaeW_at_Onyx Coach

@MarkSnodgrass Both CurrentDate and CurrentTime will default to midnight if you change their Type to DateTime. Think about it. If DateTime and Date are the number of seconds since Epoch, then Date would just be the number of seconds since the day started. It's the same in excell when you show a column that was previously…

@sky00221155 the NULL handling on aggregate functions (like AVG) is the same as in Excel. If the column contains a NULL, it will not impact the numerator or denominator while calculating average. If, for your data, NULL is synonymous with 0, then you have to replace the NULLs with 0 and then they will impact the average.

@MysteriousDomo to troubleshoot, break down your beast mode into it's component parts in a table for a handful of records. if this is your beast mode, CASE WHEN `Timestamp` BETWEEN CURDATE() - INTERVAL (24 - 7) HOUR AND CURDATE() + INTERVAL 7 HOUR CURDATE() - INTERVAL (24 - 7) HOUR and CURDATE() + INTERVAL 7 HOUR Make sure…

i think this stoped being developed. it's not unsupported, but with improvements to embed as well as the APIs, more clients are going the route of using standard front end developer kits for custom viz.

@user095459 I've worked on this problem for a client. You'd have to build a table that does a CROSS APPLY. So if you have data, item_id, order_id, amount you'd do a self join SELECT a.* , b._item_id from table a JOIN table b ON a.orrder_id = b.order_id and A.item_id < b.item_id

@user095459 recall. with Federated Query domo is passing the lift of processing the query back to your source system. This is no longer an Adrenaline question. This is a Your Database question. The Federated Query Adaptor will have timeout rules etc built into it's configuration, that may be what you're running into. That…

@Nick_Bertz default filter is a beta feature. talk to your CSM.

@Dylan in the immediate term contact your CSM and ask them to turn on the Dataset Views beta. DSVs functionally supersede the UI for Fusions and accomplish the same action. I doubt Domo would change the product on a feature that should be sunset (IMHO) but this will help you overcome the blocker.

careful... unique opens cannot be aggregated over time. that's not really the definition of unique. I think i would push back to the business on this one. If you measure Unique opens daily. and i open the email 5 times on separate days in the week. do i count once or five times?

This is a mind bender, but a little more bullet proof that @GrantSmith 's approach. His approach works as long as you never have more than a 6 day gap in data. But if you did you'd return the wrong result. Consider the following Example Assume you have gaps on Jan 6,7 and 9 (and 3 and 4 but i forgot to include them).…

Just in case you want to parse multiple objects gracefully. You can do this in MySQL. but actually Magic 2.0 with a little creativity supports a similar workflow with the Add Formulas tile.

@bdx it's interesting thaat Domo is choking on 100k rows. I wonder if that's a MongoDB limitation. Domo can ingest multi M row datasets at a time so it's not likely to be a domo infrastructure problem. that said, yes, use filters to try to limit just the new documents you're bringing in. You may need to implement a…

@mlanterma 1) yes, you can upload your date dimension as a spreadsheet and then add it to your dataflows. 2) you can generate one in MySQL with the columns you need (this is what I implement for clients) 3) you can use the Date Dimension as @GrantSmith recommended. No matter which method you adopt, the point is to create…

couple things here. 1) restructure your data. For maximum flexibility and recycleability you want the structure [item no], [Date], [Status], [Count Status] If you can't see the data at the item level then: [Date], [Status], [Total Count Status] You can accomplish this with a Collapse Columns or Dynamic Pivot in Magic ETL.…

@Bianca_M , @GrantSmith 's recommendation to LEFT JOIN in a Date Dimension is accurate but also a bit more nuanced. If you add in a Date Dimension you'll basically guarantee that you have one row for each date in your dimension table and IF there's matching activity you'd fill in the right side with information about that…

@imelendez i'm late to the party, but this video has been there for ages: https://www.youtube.com/watch?v=JNQFZCj8JcQ

while @GrantSmith 's solution will create the buckets, you will still have one row on the axis for each value. it will not aggregate to the bucket level. (in other words you'll see 'other' and 'name' multiple times on the axis instead of having one row "other" and one row "name" if that's your desired outcome, you must…

when you ingest the data into Magic 2 (input tile) you can set NULL handling behavior.

FWIW AFAIK the PDP polices are inherited at the Dataset Level (not the DSV). Course that opens interesting questions when a view combines data from multiple datasets or PDP enabled columns ... would recommend testing to see what the behavior is there.

Be advised, in your viz you're showing Josef / Milk = 0. A GROUP BY tile won't show the empty spaces, it will only show where data exists. To include NULL spaces you'd have to include the universe of combinations.

@lowrydomo depending on your data COALESCE / IFNULL could work, but most likely a simple CASE statement would be an easy way forward.

@GrantSmith and @MysteriousDomo , just be careful with timestamp conversion. date functions in Analyzer may or may not calculate current_date based on UTC (as opposed to whatever timezone you're in. do confirm that your assumption about what Interval (24-7) returns is correct. also i don't believe you can use the BETWEEN…

@user30972 , you are correct, mid Spring, Domo changed functionality so that PARTITION on calculated columns, month(SoldDate) no longer functions.

@ST_-Superman-_ i had 2x options! but for good measure, here's a third. 3) i suppose you could preaggregate the data at the rep level and then add category metrics as columns. (but eww gross pre-aggregated data :P )

@JustinB by building your aggregation INSIDE the CASE statement you're applying the CASE statement after aggregation. (i know that sounds like i said the same thing twice... point being, what you're asking for is "after i aggregate the data, i want to substitute the results with a number and then i want to aggregate…

@walker_page , the credentials you use to access the mysql database determines which data 'Domo can see'. it's the same as if you login to mysql with any other IDE, the credentials determine your level of access in the database. so the solution, in your case, is not to 'control Domo's access' so much as change the level of…

a LEFT JOIN will only match values from your webform that actually exist in your Transactional Data. Make sure to validate that the new values you added exist on the LEFT side. When you update a webform, it can sometimes take a few seconds even minutes before the actual data is updated in Domo / VAULT (storage layer). Make…

@Martellooooo , this is dangerous and can lead to heartache down the road. As @GrantSmith indicated, Domo stores (and therefore expects) data to arrive in UTC. If the data isn't in UTC as it enters Domo, then i guess... sure transform it to UTC, but be wary of daylight savings. in the Viz layer, Domo adjusts the data to…

you need that LEAD() window function we did in MySQL + CASE when Status = B

@Joe_M there a handful of solutions 1) remember drilldown can go to a card built against a separate dataset 2) take your entire dataset, UNION it to itself, then fill the Salesperson column with "Group Average", then set up PDP policies such that I have access to my own data and "Group Average". I see myself compared…