jaeW_at_Onyx Coach

@reichner015 you can use pieces of everyone's advice to solve this. sum(sum(amount)) over () will create a window function that allows you to look over all the rows in your dataset. build your CASE statement in place of amount to calculate your lifetime win rate. to isolate transactions in the n-th month of the quarter, i…

@AlexF Domo has two ingest methods when receiving data, either a full REPLACE (all rows in the dataset are truncated and then the new data is brought in) or APPEND (rows are tacked on to the bottom). That's it. There is also UPSERT or PARTITIONing but both of those are based on the APPEND concept with a little data…

@GrantSmith the downside of this approach is that in order to show the result, each userID would have to be displayed on the axis. it might be cleaner to actually build the cohort identifier (we're assuming the min(dt_api_request) into the dataset. this can either be done in ETL OR JOIN in the attribute using a dataset…

@Johnson i really don't understand what you're trying to accomplish. can you explain your goals in 'plain english'? how are you defining a cohort? what are you trying to track? how many made 100API calls? regarding "100 other api calls" what does that mean?

@genaussie , while outer joins are nice, on most days of the week it is completely inappropriate to use an outer join. i appreciate that it'd be nice for the feature to be supported because ... it's SQL, but in the over 200 projects i've implemented for clients LEFT or INNER JOINs have been sufficient. In my experience,…

@reichner015 . Grant has good advice. DataMaven's beast mode is problematic b/c she's using nested SUM() functions without an OVER clause which will have unexpected (if any) results. 1) be clear about what you want to show. do you want to see one row per quarter or one row per month? (start with a table card, and build…

@EmilyPK it sounds like Domo is only rendering a subset of the emails you want to see in your filter card. (expected behavior) Create a beast mode that only includes (or excludes) the desired emails. i.e. CASE WHEN email NOT LIKE '%hotmail.com' then email END or something like that.

@Daneia ... can you be more specific about how thee data does not align with what you're seeing in the platform? are you taking into consideration the curing period in APIs? Are you confident your Domo connector configuration is a 1:1 match with the query being executed in GA? I'd start there. I've definitely worked with…

oh. fun project. i see it as two steps. 1) you'd have to get your chatbot to translate your question into a dataset with a set of filters. 2) you translate that response into an API request. the second part is not so hard. the first part though ... if it were me i would have one table that has my data pre-aggregated such…

thanks for the mention @GrantSmith

@Adrien most days of the week if you're doing SUM(DISTINCT) you've designed your data ... poorly. usually this happens b/c people JOIN their Actuals to Daily_Forecast and then use SUM(DISTINCT) to avoid double counting targets. Don't. UNION your datasets. If you continue the road you're on, if you had two separate products…

@user095063 if you can't write SQL, i strongly recommend you learn your way around Magic 2.0. The more SQL you write, the fewer people at your org can support you as your queries become more complex. If it were me, i would 1) Trans_Table = union all my data together with a column for 'Activity Type' 2) Date_DimByType =…

I don't quite understand what you're trying to accomplish, but this is syntactically incorrect (for analyzer) ... it's fine for standard SQL. But this window function MIN(AOP) will not aggregate as expected. SUM(CASE WHEN LEFT(`Stage`, 1) IN ('3','4','5','6') THEN `Pipeline` END) / MIN(`AOP`) OVER (PARTITION BY `Selling…

@MichelleH Domo Publish would probably be the safest way to make sure you don't accidentally share content with users. Tell your AE you want to talk to Dan Hendriksen and then tell Dan, Jae W sent you ;) But also, there are commercial implications for Publish. You can also take a look at Publication Groups which is an…

you cannot do a count distinct across all your cities even with a window function. the window function would calculate count distinct for EACH city, and then take the average, min or max as you've tried already. unfortunately (as i'm sure you've figured out) if i have the same retailer code in multiple cities, it would…

sorry i meant Mega Table

There is a setting under General settings that will allow you flip how your Pivot Table displays metrics ... not sure if that meets your requirements. EDIT -- Lies. you can do it with a HTML table card. "transpose"

@user01109 it's the exact same code / solution. just JOIN on customer instead of order.

@DomoNewbie /@MarkSnodgrass you generally shouldn't put an aggregate function inside a CASE statement. the reason why your final solution works is b/c by using the aggregate functions in Analyzer, functionally you're moving the aggregate out of the CASE statement. you could write your beast mode as: sum( CASE WHEN `LOB` =…

@Liliana i strongly recommend against using Magic to uncollapse the data. if a new payer shows up your dataaflow will exclude them, b/c you haven't enumerated them. Pivot Table is a safe solution but becomes unwieldy as you get more and more payers. What's the question you're trying to answer?

@user018766 , Form Builder stores the data in a noSQL database (AppDB, https://developer.domo.com/docs/dev-studio-references/appdb). If you've heard of MongDB they are very similar. you don't have a document, like a word doc that you can access. what's the rest of your question? why do you want to access 'a CSV'? (it's not…

yes. use the Java CLI , https://domohelp.domo.com/hc/en-us/articles/360043437733-Command-Line-Interface-CLI-Tool , query-data will allow you to form your query using SQL-esque language. OR consider using DataSetViews to create subsets of your data (and apply dynamic date filters).

@Ashleigh , @MarkSnodgrass and @GrantSmith are on the money for very different reasons. 1) as your query get's larger, workbench execution times are 99% subject to the performance of your source database (how long does it take to retrieve your data from SQL). If you're parsing 82million rows on your source database you are…

@melyeo are you confident that the 'title' column never contains NULLs or blanks? I suspect Domo's code may not have good error handling built into it. So make sure your table, hazard, does not include rows where title is not populated. this video walks you through how you could calculate this from scratch step at a time.…

@leeann_flowers and @Nick_Bertz there currently is no way to set a default value on a filter card. you can set a "Default Filter," but no, you cannot stop people from un-filtering a column. If necessary, consider adding text to your dashboard that provides instructions for use.

@Khan and @GrantSmith there are features for Median() and NTile() in Analyzer, just ask your CSM to "turn on Median in Analyzer"

@Jbrorby make sure to check your results on edge cases with numbers that you know the answer to. You're doing alot of JOINs and GROUP BYs... sounds pretty dangerous because you might be allocating rows to unrelated data or double counting in some cases.

@MarkSnodgrass on paper you're probably right, but i think i would try to ascertain the granularity (what one row in each table represents) of @pangeran_zuko 's dataset. IF the granularity of Credit Limit Usage is one row per day per customer IF the credit limit has been used, then yes, the LAG() approach would absolutely…

Try other chart types. From what you're displaying Domo is working as expected.

glad you figured it out! "IF colB sometimes contains 0 or NULL you need to implement error handling because you will have errors." CASE when `Total Views` IS NOT NULL AND `Total Views` > 0 AND `Total Conversions` IS NOT NULL and `Total Conversions` > 0 THEN `Total Views`/`Total Conversions` END SIMPLIFY YOUR MATH. The root…