jaeW_at_Onyx Coach

@user17774 have you looked at the other reports you can generate in Domo Stats (People)? or Domo Governance (Users and Groups)?

@msharma think of analyzer as an engine for writing a SQL query. When you add filters, you're adding a WHERE clause. Your Beast Modes are part of the SELECT clause. in SQL your execution order is FROM - WHERE - GROUP BY - SELECT - ORDER BY. Same applies in Analyzer.

what happens when you don't use SELECT * and actually select column names?

@nicangelica this one is a brain teaser. assume we are trying to capture the last time 'cost changed' 1) window function calculate the previous cost (lag, 1) 2) create a binary (isCostChanged) using a CASE statement. 3) take the cumulative sum of isCostChange using window function (sum). at this point you should basically…

@user030156 , change your aggregate in analyzer from the default of SUM to AVG.

I don't think this is possible without preaggregating the data. to apply conditional formatting, you'd have to be able to wrap the aggregate in a CASE statement CASE WHEN sum(amount) 10 .... END But Domo broke that functionality a few weeks ago. I don't know if it's been restored.

i wouldn't do this in MySQL anymore, I'd do it in Magic 2.0, but this will get you the results you want. Basically your initial dataset A1:b3 Duplicate each row by the number of commas (antigens) order_id 1 has 3 antigens, therefore duplicate the row 3 times. then keep the nth antigen. your granularity should be one row…

this is the reason why i try to avoid using period over period cards and instead always recommend building your own date dimension that handles period comparisons for you. https://www.youtube.com/watch?v=CDKNOmKClms

@Sam1 , @GrantSmith is correct insofar as the access token you generate (or client_id & secret combo) will have the same permissions as the user with which the token was generated. to control which rows of data a user has access to, use PDP policies. to control which APIs a user can use (what actions they can take in Domo)…

just build your own week-based date dimension. then construct an offset calendar.

@Fatias MySQL 5.65 is several years old. I'm pretty sure it's safe to say it's not a priority to upgrade the version of MySQL to something more modern. you don't have to implemen using parent_id < id. you can implement where you accumulate the path concat(path '+', id) as path and just filter WHERE path not like '%id%'

@Kumar619 it's a feature that needs to be enabled on the instance, contact your CSM or support and ask them to enable "window functions in beast modes"

I assume you checked the obvious culprits like making sure the card renders and removing any erroneous beast modes that error out? Have you tried sharing the card with the user first? If the obvious things fail, send a support ticket, they have the right tools for debugging bugs.

APPEND your data instead of JOINing it. https://www.youtube.com/watch?v=PVbOeLSae9o

@serendipity if it were my requirement, I would have two separate cards for two different audiences. (AND still apply PDP as necessary). Also, check in with your CSM, I believe Domo were working on column based PDP but I don't know how far they got on that.

Data in Domo doesn't reside in a RDBMS system. So you can't create an ERD like you would in other SQL environments. THere is a Governance dataset that will give you schemas,…

@swagner , you can't finish your requirement just using window functions in analyzer. @GrantSmith is hinting you in the right direction. You're trying to functinally filter on an aggregated value (if the sum of activity in 2019 and 2018 is greater than x then .... ) If you've got 30M rows you can still get away with…

uh... it sounds like you're describing interactive filters, but you'r use case is pretty... vague. in Domo it is possible to set up a filter that applies across multiple datasets. so if i have two different datasets with a column called Color... i can filter on "color = red" and then filter both datasets...

@swagner this would be a 'usual suspects' type question if you asked a TAM. Can Vertex automate emailing to you? (email connector) can Vertex set up an automated file drop? (FTP, Amazon S3 or similar storage)? I didn't look closlely at the documentation, but it sounds like Vertex provide a CLI (a cli presumably would allow…

Sorry @GrantSmith and @MarkSnodgrass hard disagree. Don't JOIN the data. APPEND them. If you get to the point where you have multiple rows per week OR if dataset 1 is at a different granularity than dataset2 then you end up with row growth (because you're JOINING) and you have to crazy math to 'SUM DISTINCT). 1) add a…

@banderson use UPSERT + Domo CLI https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool#section_34 https://www.youtube.com/watch?v=kHSeSbSFS1Q (sorry less polished video, but gets the job done).

just FYI @DP_DynamicData , the documentation has been updated since 2019, https://knowledge.domo.com/Connect/Connecting_to_Data_with_Connectors/Configuring_Each_Connector/Connectors_for_File_Retrieval/JSON_No_Code_OAuth_Connector, to incluude the URL @Ben_Dunlap documented in the comments.

@user041053 use regex to identify if a set of characters are formatted as a date. then use INSTR or SUBSTRING to try to extract the set of characters then use STR_TO_DATE to convert the extracted values to date.

@user069416 , you're mixing issues. 1) use PDP to determine what people can see data. 2) sharing is sharing. assume that eventually, EVERYONE will share EVERYTHING and therefore everyone will see everything that doesn't have PDP policies applied.. Therefore, to secure your data, focus on limiting what people see via PDP.…

@MarkSnodgrass is pointing out that an empty string, '' <> NULL. hence his test: TRIM(ID) = '' or ID is null HOWEVER, SUM ... 1 else 0 END will give you COUNT not COUNT (DISTINCT). @ekola , you should validate whether COUNT DISTINCT is including NULLS or not, because standard behavior in many SQL implementations is to…

Given the math you've put on the screen, there is no reason to run this ETL as a SQL transform. Just keep your data unaggregated. Be careful with Sum(`Early to Lost`+`Early to Won`+`Early to Late`) AS 'Total Low to High' for any row, if any column contains the value null, the entire result will be null. this is the…

MySQL 5.6 does not support CTEs. this approach works.

Sure. just store the qeuery as table, and CROSS APPLY it to your table. Not sexy, but gets the job done. select * from `last_date_of_spent_data` or SELECT * FROM table , ( select max(date) as LastDate from `last_date_of_spent_data`) ld

@HowDoIDomo You want this video, https://www.youtube.com/watch?v=CDKNOmKClms&t=325s to build a period over period analysis without using the POP chart type.

@user09644 you could use list-dataflows and output to a csv. then use any other scripting tool (python for example) to continue the automation process. @MarkSnodgrass is correct, the CLI is mostly a wrapper for interacting with the APIs, the querying and automation part is the next mile which you'll pick up with Python,…