jaeW_at_Onyx Coach

@dacorson re: case sensitivity. keep in mind that Analyzer is a UI for building viz AND defining queries that get to SQL. it is possible (though I am uncertain) that when you create a beast mode that the validator checks for case sensitivity; however the database itself is not case sensitive for column names. i don't know.…

just stack your data with a date dimension. the gist of it is. create a datastet for one row per date in YTD, and in clude a column "Period Type" = YTD. then UNION all thte dates for Period Type = MTD or and maybe another set of Dates where Period Type = Prev Year YTD

i'd start by confirming that sum(sum(1)) over () yields the expected results. from there it's probably an issue with your column spelling. alternatively, try using a column that doesn't have a $. that is a special character and probably should be used in a column name for this exact reason.

you can't really enforce data types using add Formula. well no that's a lie, you could use the CAST() function. If it were me though, I would use the ALTER COLUMNS tile. This way you have more granular control AND are not mixing and matching tasks. use Formulas to define formulas. use ALTER COLUMNS to set schema properties.

... that's a pretty roundabout solution and is probably not tied to the root cause of the issue (unless you were mistyping column names) Analyzer should not recongize a difference between Magic and Redshift generated datasets, b/c once the dataset gets loaded into Adrenaline (the database layer) there is functionally no…

ask your CSM to enable the MEDIAN function. There are a handful of statistic functions that can be enabled in anlayzer. Also https://datacrew.circle.so/c/developerdomocom-community-edition/supported-functions-in-adrenaline-dataflows-views-and-beast-modes, you might find what you're looking for in Adrenaline DataSet Views.

not entirely sure why you need to change the sort order on columns ... but you could also consider using DataSet Views to reorder columns. it will be more performant than just running ETL to change column order.

if you waant the total of all active statuses you need sum(sum(1)) over () this will give you a grand total for all rows. if you want a grand total of only active statuses sum(sum case when Status in ('choice a', choice b' ) then 1 end )) over () these are window functions which is a feature you'd need your CSM to enable…

so put [Staus Date] in columsn. put [status] in rows and just do a sum(1) as your metric. (remove the other metrics).

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