jaeW_at_Onyx Coach

@GrantSmith and @MarkSnodgrass i love both of your solutions (especially the regex!) if it were me, I would have done something like, CASE WHEN <text contains 3 colons> then text WHEN <text contains 2 colons> then concat 00: END then use TIME_To_SEC

@akshayaviswanathan , there is no 'easy' way to do what you're asking for. @GrantSmith 's method of using the beast mode manager works IF the beast modes have been shared to the dataset (because then you only have to update one beast mode). if you didn't share the beast mode to your dataset, then do so. and then edit all…

you cannot set background colors on header columns and rows. in the standard table chart type, you can apply HTML formatting to your beast modes, https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/Adding_Graphics%2C_Links%2C_and_Images_to_Table_Cards_Using_Beast_Mode @LMS…

... so use an order by clause on quarter if you want it to sort by quarter.

... do you have quarter in the order by clause?

@user066906 the code that everyone gave you is correct. the problem is in your understanding of your data and how you're testing your data. if you can't identify your data with s not null and empty string, then it must contain SOMETHING that you aren't testing for. figure out what the something is. alternatively, post a…

do you have Year in the ORDER BY clause? if so, then Q2 could be from different years. remove everything from your ORDER BY / sorting section of analyzer.

@LMS , in the future, please be specific about what you're unable to accomplish (other than 'everything') it seems like you're asking us to provide consulting services -- which i don't think is a reasonable ask from the community site in my opinion. you can calculate cumulative sales in your ETL or you can do it in…

It happens, unfortunately. Try refreshing your page and recreating the card. If you have Beast Modes saved to the card that do not validate, this can cause problems, so make sure they are all good.

@AJ2020 so... instead of having the denominator be the grand total by item you want to subtotal by item and month Window functions are not unique to Domo. It's a common SQL thing. Here's some documentation so you can understand syntax sum(sum(amount)) over (partition by item, month)

Hey @MarkSnodgrass , i think i found this video of you explaining how you created that ZIP file!! :P Side note we should put that in dojo-domo. https://www.youtube.com/watch?v=eKOLhsfl10Q

support ticket :(

@GrantSmith when you write the index_o we should give it an explicit column name first?

@user066906 are your invoices and payment records on the same row? or are they spread across rows? if it's spread across rows these solutions we're proposing won't work for you... so ... make sure to double check. @MarkSnodgrass 's checks for is null OR is blank are perfect, but just do the opposite check as proposed by…

@user063136 post a sample dataset.

@AJ2020 you need window functions enabled in analyzer! Talk to your CSM. grand total sum(sum(amount)) over () to add subtotals by items sum(sum(amount)) over (partition by item) % of total would just be division sum(amount) / sum(sum(amount)) over ()

@kjones140 this is an interesting idea, but @GrantSmith the part that makes me nervous is that execution would have be be based on whether a previous ETL is up to date. to know the answer to that question you'd have to have up to date info about the state of your datasets. Domo typically only wants users to update their…

mebbe try recreating the card... sorry not helpful.

@LCash no ... that implementation of the case statement should be fine.

there is not a way to conditionally trigger dataflows built into the UI yet. i have a feeling that DP21 they are going to fix this. but maybe that's just me being unecessarily optimistic. if you know python, you could use PyDomo to orchestrate dataflows with more control https://www.youtube.com/watch?v=oT5NipvWK1o

@LCash i think domo made some changes about 3 weeks ago that made cards in analyzer a little weird. try re-applying the grouping in the date settings (group by quarter). random question, do you have a CASE statement in your beast mode? CASE ... then sum(amount) ... END? ultimately... submit a ticket to support, but yes, i…

@user094816 , no if you create a beast mode on an aggregate, no you should not try to build a calculated column off of it. Sometimes it works, usually it gets weird. you can create this in ETL using a GROUP BY with a COUNT, and then JOIN the GROUP BY tile back to the ungrouped data. from there you can easily build a CASE…

Instead of Substring try LEFT(description, <positionOfSpace>)

Luke ... i think your problem is your WHEN statement you've got it's ALWAYS grreater than or equal to zero so it will NEVER get to your ELSE clause. CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN MarketingClubLevel ELSE SUBSTRING(MarketingClubLevel,1,(CHARINDEX(' ',MarketingClubLevel,1))) END AS ClubDescription…

@Nek , Domo MySQL dataflows run on MySQL 5.6 databases. If you write valid MySQL code it should run. Your code looks fine so I'm surprised you're not getting results. Keep in mind the preview will only show the first 100? rows AND default behavior when building dataflows is for domo to only ingest the first 10k? rows, so…

@MarkSnodgrass a little late for the party, but here's an alternative to doing a full on cartesian product. i'm assuming your dataset is relatively small so multiplying Table A times the number of rows in Table B isn't THAT big a deal. For a solution that scales (in certain use cases), you can duplicate table C and N by…

it would be a pita trying to get everyone's time entry to line up. if it were my task i would create a dataset with one row per half hour per day (48 rows) * 7days * number of employees then for each block i'd add attributes "what where they doing" and busy or not. Then put it in a heat map.

i think your result is correct even though it's not what you want. start with a table card before you do a pivot table. sort your card by year, month, count(cert) desc in your validate whether your original rank() is giving the correct result. if not, try sum(min(1)) over (partition by…

translating 'what xyz platform is reading out of my odbc driver' can be a pain to start troubleshooting, break your query into pieces. SELECT DISTINCT MarketingClubLevel, -- is my query identifying the space? CHARINDEX(' ',MarketingClubLevel,1) as positionOfSpace, CASE WHEN CHARINDEX(' ',MarketingClubLevel,1) >= 0 THEN…

@AndresChavez you probably have a syntax error. and your code is hard to read because you didn't format it. keep breaking apart your beast mode into it's composite parts (the two CASE statements) and work backward until you find the point of failure. BTW if you use three tildas , `, you can insert a code block like this…