jaeW_at_Onyx Coach

PDP was not designed as an alternative to Filtering... i think organizations get themselves into trouble when they start mixing up the two. Take a look at building Beast Modes or using Webforms fusioned to datasets where you define custom groups to create custom groupings... but i strongly recommend against trying to use…

Part of the issue can be related to caching. When you query a dataset in adrenaline, Domo starts caching some of the results. In my mind, i think of it as analagous to the Statistic tables you build in a SQL database or the cache you have in MS SSAS MultiDimensional. So, the more questions you ask the faster Domo gets at…

Is this a Domo connector or one that you wrote? I'm surprised that Domo didn't auto_adjust the duplicate column name OR fail the ingest. If it's a connector you wrote, consider reshaping the data to have more rows and fewer columns. That's going to be a real pain to build analysis against. If i had to guess you've got…

@GrantSmith , Just do the JOIN on a small handful of columns, start with 2 columns from the LEFT and the RIGHT side. Keep in mind SELECT * across two deep tables can be ugly ... especially if you have wide tables. Validate that you get the expected number of rows (14.7M) in the output. It's wild to me to have 14M…

@MarkSnodgrass is defo right to check the datatypes assigned in the Schema. Keep in mind that in your SELECT statement if you divide an INT by another INT (integer) it can also truncate decimals, so make sure you're following the rules of how SQL works and assign an appropriate datatype. Also, the Domo UI can be a little…

In Magic: create a copy of the column converted to TEXT and do a REGEX / STRING transform. In MySQL: use date_part functions (like hour). Best Solution: split date and time (HH:mm) into separate columns during data ingestion. THEN use a Fusion to join a Time Dimension table (where you have one row for every minute of the…

@texas , @GrantSmith nailed the answer on the head. Only thing you'd want to do is fill cases where the START or END date are NULL. (usually for EndDate I'll set it equal to today). If you don't, you'll eliminate all open projects.

Ben Schein covered this during a Demo at the most recent Domo EMEA User Group meeting (see the recording in the linked post). https://dojo.domo.com/t5/EMEA/The-next-DOMO-EMEA-User-Group-Meeting-is-16-4-2020-at-1430-UTC/m-p/47849#M1 the Data Explorer views beta will make it possible to export datasets with 'beast modes' as…

@SeanPT is correct. As i understand it, unless specified otherwise, Domo will ingest your data and assume it is in the timezone of your company settings. From there it will store the data in the data layer in UTC. The Visualizations will use company settings to display 'the correct timezone.' If being able to display…

take a look at the list-report command in the Java CLI , does that get you started?

@user073741 , i would start by paring back my stored proc to ensure that i had the proper syntax for CREATE-ing and calling a stored proc. keep in mind we're on mysql v 5.6 i believe. THEN i would take a really close look at the error message. it says <number> FROM. I would guess that you have values in your table that…

concat( '<a href="mailto:' , case when rand()> .5 then 'someone@example.com' else 'hello@example.com' end ,'?Subject=Hello%20again" target="_BLANK">Send Mail</a>' ) both of these work fine. are you certain your fields don't contain blanks? CONCAT( '<a href="mailto:', (Case When Length('jae@domo.com') = 0 Then…

not the answer you want but definitely should open a support ticket. Check and make sure you have ownership of all the cards? i'm not gonna lie... no idea.

AJ, "A column in this calculation does not exist" says it all. there's a column that doesn't exist. if i had to guess your CONCAT is not concatting the way you expect it to, probably a mix up between " , ' and `. build your concat statement without the case, and slowly add pieces of the HTML / concat statement until you…

No, unfortunately Domo cannot be run on local infrastructure. you can use federated query to keep all your data on premise, but that's as close as it gets.

CASE statement? Case when sum(amount) <=0 then 0 else sum(amount) end

@Simon_King i assume you're trying to create a custom connector? https://developer.domo.com/docs/custom-connectors/connector-dev-studio takes a hot minute to get into the tutorial section, but i wrote an article to build a spotify connector from scratch:…

@WizardOz check in with your CSM. It should be available.

Fair enough, if you must use Python do it. If it were me, i would have an ETL where i do the 'easy transformations' in magic, and then a second ETL where i change the granularity of the data to one row per day. WHY? 1) changing granularity is probably a requirement specific to a dashboard requirement. Whereas, a cleaned-up…

'Filtering on aggregates' is a Feature switch that can be enabled by your CSM. It comes with the caveat that it is a beta feature that hasn't been fully developed and integrated into analyzer so some functionality around exports etc. won't work. Because of the caveats, I personally don't use it often and instead try to…

SQL TO THE RESCUE. Use a JOIN on BETWEEN. SELECT t.* dd.date FROM date_dim dd JOIN transactions t ON dd.date betweent t.startDate and t.endDate EDIT:: can't remember if mySQL supports BETWEEN SELECT t.* dd.date FROM date_dim dd JOIN transactions t ON dd.date >= t.startDate and dd.date < t.endDate

@GrantSmith could you do something like sum(1) as daily_record_count, avg(sum(1)) over () as 60_day_rolling_avg , ( sum(1) - avg(sum(1)) over () ) / avg(sum(1)) over () as percent_Change FROM ... WHERE DATEDDIFF(date, current_date()) between 1 and 60 GROUP BY date EDIT:: pardon my pseudocode, i'll try to test later.

You can use beast modes in filter cards. You can't use aggregations, sum(amount), in filter cards because that would require the ability to filter on the HAVING clause (which is a feature switch that's not enabled by default, talk to your CSM!) Side note about said feature switch: - Export to excel and PPT is not supported…

Hey Martin, If i had to guess you have an error in when your beastmode evaluates the concat. Can you confirm that Round(BeastMode*100,2) delivers the expected result in a table card? Does your CONCAT function wrap an aggregation or does it evaluate at the row level? consider the differnece between: concat('text',…

Instead of trying to use drill paths to solve this problem, could you use filters? could you structure data where you have a column where you pick between 'day, month, and quarter' which then filters the data to the range of dates you're interested in seeing? if you must have different views with different groupings in the…

it's unclear to me what you mean the rows are duplicate. does the underlying data actually contain duplicate rows? or do you mean, you have more cards in your visualization than you expected? If it's the later, i'd guess you have Date in Sorting (or a similar period based column) I'm guess that because adding a field to…

@eric_etumos wrote:It looks like a simple syntax issue: 100 * (SUM(`revenue`) / SUM(CASE WHEN `day` = '01/01/2020' THEN sum(`revenue`) ELSE 0 END)) EDIT:: i need to read everyone's responses before i start replying ... looks like @GrantSmith beat me to it... The ELSE clause is a red herring. If you take the SUM of a column…

Tricky! But dangerous if the changes are not transparent. Consider using the fiscal calendar .https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Using_A_Fiscal_Calendar Keep in mind, using a fiscal calendar DOES NOT change how DATE functions in Beast modes evaluate.…

@Cartergan wrote:You can try limiting the data rows and see if this achieves the result you are looking for. In the analyzer view near the bottom right of the screen you can set how many rows you'd like to show, and then you'd need to sort your results how you'd like them to appear. I would be unsurprised if this method…

Two thoughts here, While you CAN save a copy of the cards onto a new page... I probably wouldn't. This creates a maintenance nightmare that you don't want to think about if you end up with 3 or 5 dashboards that are 'different views for different departments but functionally the same information.' Instead, consider using…