jaeW_at_Onyx Coach

ooof this was fun little challenge! https://www.youtube.com/watch?v=eifSYZIcPzg the short answer 1) you can't do it 100% as beast modes in Domo (you can get 75% there with window functions but it's not close enough). 2) you should probably do this in magic 2.0 (beta) it'll be super fast if your data isn't streaming and a…

oh ... well that makes it easier i guess. sorry i'm half looking at this at 9PM ? You can do this with a LAG function in a beast mode. Contact your CSM to get windowed functions enabled on your instance. https://www.youtube.com/watch?reload=9&v=cnc6gMKZ9R8

? they can't charge you if you didn't put in a credit card! no you won't get charged.

yikes... this is ... not the ideal way to structure your data ... but we can make it work. Goal: identify the record that = the first day of the month and the last day of the month then caculate the average between the two. If it were me, I would recommend adding these collumns to a date dimension and then joining the date…

? @Microsoft-Dude ? subtle dig in the profile name? If you're a PowerBI guy, I'd love to have a conversation with you sometime, I need to do a YouTube video on "getting started in Domo for PowerBI developers" b/c building datasets in Domo is SO DIFFERENT.

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Beast-Mode-Sliding-Window-Function-with-Partition/td-p/48407 This thread and associated YouTube video will get you started. Make sure to contact your CSM and ask them to enable windowed functions in beast modes.

@shamal an Admin is an Admin. There is no concept of a departmental admin in Domo ** yet ** but it is a feature they're working on. In the immediate term, you could create a custom role (talk to your CSM) which gives more finite control over grants.

AFAIK ... depending on who you talk to at Domo the answer may flip flop depending on where the stat is collected from. But I believe the correct answer should be Data Fusions DO NOT count. (I say this as a former employee of Domo, but you should check in with your CSM, and make sure they give you an answer in writing ? 

... Domo does have white labeling options ... but it's a for pay conversation. @chrishaleua 

@Simon_UK i've approached this problem several ways, applicability is dependent on your requirements. My assumptions. 1) you have a lookup table with a field Tag containing a list of tags (RED, BLUE, GREEN) 2) your transactions have a column Tags with a semicolon delimited value containing tags. GOAL Use a checkbox or…

this is not the question you asked, but i strongly recommend you calculate your own projections in Domo using ETL. WHY. if you rely on a projection based on actuals data visualized in chart, you literally have no way of validating whether your forecast method is good or bad. Consider this example, i ask you to give me all…

(domo governance) DG Dataflow Details will tell you which datasets are used as inputs into a dataflow. JOIN that to the DataSet dataset to identify which datasets are not used in ETL. Be careful of the treatment of fusions if you use them, b/c they don't follow the same dataset input / output pattern. consider metrics like…

Build a separate table, a date dimension, that has one row for every day since the dawn of time and then explicitly label the month and week number based on your requirements. Then JOIN that date table into all your dashboard datasets. It'll be MUCH easier than this CASE statement nightmare you're building for yourself!…

your use case sounds convoluted and it sounds like you have conflicting filters. If i understand you, in the CARD you're filtering for the last 12 months. in the DASHBOARD you're filtering for the CURRENT Year. The dashboard will override your filters and limit you to data from the current year, (i.e. 9 months). Best…

@gbennett ,the problem is literally in the error returned, invalid column name. SELECT DATE(`order_date`) - INTERVAL (DAYOFMONTH(`order_date`)) DAY + INTERVAL 1 DAY AS giveMeAName ,SUM(`order_value`) AS `Punchout Revenue` FROM TABLE If you don't name your column (the AS clause), Domo will try to fill it with the function…

This is clever! but it sounds like you're trying to change the styling on Domo cards. Is that right? That i'm confident is not possible without buying white labeling features, but that probably isn't advised as it would over write styling on ALL your visualizations. Have you tried using explicit styling inside your IMG…

@GrantSmith and @user063136 , judging from the results of your original post it looks like, for one parentID there can be both Retail and Wholsale loan originator types. If that weren't the case, then you shouldn't get multiple rows per parent. "Retail" (CASE when `Loan_Originator_Type` = 'Retail' THEN 'Yes' else 0 end)…

to tie it all up, it sounds like you want to do a visualization that counts the bucketed aggregation of a metric. Unfortunately you can't do that in Domo, because effectively you're asking anayzer to do a GROUP BY twice. SELECT Buckets count(*) FROM ( SELECT Course, Department CASE WHEN count(*) BETWEEN 1 and 2 then '1-2…

i'm still unclear what you're trying to report. you could just add a row to your dataset for the last recorded value of each day and set the time for midnight. or instead of dividing your dat into seconds ... aggregate it into minutes or 15-minute blocks. at the level you're visualizing your data at, this high granularity…

To get a Total Row that's not a 'total row' in analyzer, you'll have to do a little tomfoolery with the data. Just APPEND a copy of your data to the bottom of your data, but replace LOB value with the constant 'Total'.

if time is important, structure your data during ingestion to have a separate date and separate time column. is this a streaming data / low latency use case? does the data update hourly? if yes, ETL may not be an option (hence, ingest the data with a separate time column). For this use case I think I'd try to covert the…

Oh ... let's be clear here, in Beast Modes / Adrenaline, Domo IS NOT using MySQL. Adrenaline is a database engine built on a blend of proprietary and open source technology. The function implementations MIMIC MySQL syntax and functions, but it IS NOT MySQL. (seems like a stupid point, but MySQL uses row-based indexing and…

so you want to do a cumulative sum? if your answer is 'no my data doesn't support that', restructure your data so you can do a cumulative sum. then you could do what you want with a window function. sum(sum(amount)) over (partition by week)

instead of writing a beast mode with obscure logic, why not clean up the data in ETL? you don't want your data to be non-intuitive and require a data dictionary in order to use it because that means every future user has to 'know the rules' in order to build or alter a card. change your word order if "ppv" is null, then 0,…

@user065584 , i have an answer you didn't ask for ... don't learn MySQL to become more effective in Domo. Spend time in MagicETL. I get it, hard pill to swallow for a person who does scripting. MagicETL will be faster than anything you write in MySQL. And the performance of Magic will continue to improve whereas MySQL will…

It may not be possible in Sumo cards, but definitely acchievable in anlayzer. https://www.youtube.com/watch?v=ZPf41Fjn1H8

I would restructure this data in ETL. If Tax is the same across all invoice lines, take your Invoice Lines and Remove Tax. then add a column with a fixed value of "Invoice Lines. Then UNION a copy of the data with one row per Invoice for Tax. you can put amount into the same column or have a separate column for tax amount.…

In Tomo's example they are doing a row_number() over the marketing_id column (this can be done in SQL or Magic ETL, but given what you described, I don't think that's what you're looking for. @ST_-Superman-_ said it best, for problems like this, a sample of data would be most beneficial. if i had to guess, you have two…

Just FYI i have an entire tutorial playlist dedicated to tackling financial reporting in YouTube. If you're trying to construct an income statement using transactional data, here's a video that might help you get the layout you're looking…

... "the best way" ... please appreciate that i'm operating off of a napking drawing :P. if it were me, yes, this is how i would structure the data. i might try to find shortcuts like excluding outputting rows where subscribed = false. but with the information available, yes, I believe normalizing the data is the better…