jaeW_at_Onyx Coach

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

@user048760 , you've stumbled onto the age old problem. :P 1) i assume that the price list report changes / updates regularly (let's assume weekly). at some point you'll probably want to ask "what did these metrics look like 5 weeks ago." to do that you'll need to have accumulated the history of this snapshot report. add a…

@user077529 this looks like a duplicate. can we close it?

@user052846 three things ... there are a couple products in domo that can use python, in the future it might be useful to include screen shots of your setup to establish context. given the error message you provided, is the preceding tile in your dataflow called 'original' ? # read data from inputs into a data frame…

in addition to @GrantSmith 's answers you can take a look at the ODBC connector, https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Other_Methods/Domo_ODBC_Data_Driver, there are commercial implications to aquiring access to the driver,but it does create a more SQL friendly workflow. PyDomo would probably be the…

@user07759 and @GrantSmith while lag() is a brilliant solution for getting this done, keep in mind that lag will get the previous row. IF THERE IS A GAP IN WEEKS you'll end up comparing week 7 with week 5. which ... depending on the business question may or may not be correct. If you want to calculate current week versus…