jaeW_at_Onyx Coach

do you HAVE to have it in ETL? b/c you could shape your data by creating a Date Dimension with two columns Report Date and Activity Date, such that the report date has A Day, and for each day in the Report Date you trail 50 days. So if my Date table had 5 consecutive ReportDates, it'd have 5 * 50 (trailing) Activity dates.…

https://www.youtube.com/watch?v=PVbOeLSae9o&t=748s don't JOIN your data in ETL in any fashion. Instead, STACK the data using APPEND as demonstrated in this tutorial. If you JOIN in ETL and aggregate you limit your ability to filter. If you go the opposite direction and duplicate rows, you have to do 'crazy math' to…

... if you google both "cumulative sum domo" and "running total domo" there are solutions that immediately pop up. is there something specific that stops those solutions from being applicable? https://www.youtube.com/watch?v=ZPf41Fjn1H8 // how to calculate cumulative sum. https://www.youtube.com/watch?v=cnc6gMKZ9R8 // how…

If you're using a pivot table with months on the axis, your data set is no longer 3 rows, it'll be around 3(categories) * 12(months) assuming you have data in each month... in other words, it gets weird. so don't use a pivot table if you want to limit rows that way. instead build a beast mode for each month.

are you sure the Oppty isn't being ingested into domo? have you validated that the opty is missing (by filtering on the SF object id? or are you filtering to where 'type' = null. b/c filtering on NULL in analyzer can be a bit weird.

https://www.youtube.com/watch?v=39VHAQEYHAc&t=220s Covered that requirement in this use video

Take a look at Zapier.com Here's a tutorial going the opposite direction, but the concepts are similar https://www.youtube.com/watch?v=YOaOzpXGtbo

here's a pretty extensive tutorial on window functions. https://www.youtube.com/watch?v=eifSYZIcPzg you can't filter on a rank in window functions. but you can set the data row limit to 2 or 5 or 10 rows and sort by sum(Amount) desc which is functionally the same thing. in your case if you have months on an axis, to get…

you can't filter on AGGREGATE FUNCTIONS (like SUM() or COUNT()) in beast modes without a feature switch. you can filter on non aggregate functions like CASE or IFNULL.

careful @PhilD test thoroughly the behavior at the start and end of several years. your current implementation will lump days into the wrong year. YEAR(date) * 100 + weekyear(date) THIS IS CORRECT-er BUT UGLY. Concat( Year(DATE_SUB(`Date`, WEEKDAY(`Date`))), '/Period ', ROUND( WEEKOFYEAR(DATE_SUB(`Date`, WEEKDAY(`Date`)))…

post data and a screenshot that shows your settings. I'm pretty confident (without having tested ? that @GrantSmith 's window function should work if you sort by it.

i hope you solved your problem! ... If you followed the tutorial video Grant posted, (thanks @GrantSmith ) the solution should just be a matter of wrangling MySQL code ... this is a VERY google-able issue. But basically you just need to append a Current QTD and a previous QTD Period Type to the OFFSET_DATE table and then…

weird things can happen when you copy cards that contain beastmodes. keep in mind a beast mode saved to a card is only accessible in the card. if you have card 1 and it contains a beast mode called "test" saved to the CARD. then you create card 2 which contains a beast mode called "test" saved to the DATASET. THEN you tace…

Federated query should pretty much exclusively be considered if you cannot store data in Domo. FQ comes with major ... limitations including, - you can't transform data in Domo (b/c to transform data in domo you'd have to ultimately store it in Domo.) - your card performance is dependent on query execution in your database…

use a beastmode in your filter where you replace NULLS with a text value. ifnull(column, "No Value" )

window function. sum(amount) / sum(sum(amount)) https://www.youtube.com/watch?v=eifSYZIcPzg&t=868s

https://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql Levenshtein distance is a common way of calculating the similarity between two text values (i.e. how many characters would you have to change before they are the same. "cat > rat" = 1, "John > Jon" = 1. You'll have to rewrite it into MySQL but it can…

@Khan_Naziya calculating duplicates is easy? right, just date your domo governance dataset and count grouped by the beast mode... you might want to cast as UPPER and TRIM to do light data cleansing in case i have sum(Amount) instead of SUM(AMOUNT) ... re archive ... i guess that's an interesting feature but if i had to…

OOOPS!! ERROR -- sum activity on day 5 of the cohort sum(case when date_diff(activity_date, cohort_start_date) = 5 then number_active end)/ -- sum activity on day 0 of the cohort sum(sum(case when activity_date = cohort start_date_ then number_active)) over (partition by cohort_start_date) you may be able to do this…

is your % based on the number of people on day x vs number of people on day 0? if yes then use a lag function. create a beast mode, "day 5" -- sum activity on day 5 of the cohort sum(case when date_diff(current_date, cohort_start_date) = 5 then number_active end) / -- sum activity on day 0 of the cohort lag(sum(case when…

try creating a dataset view. you may need to have two. i don't understand whether you need number of days since a point in time, OR number of days since a value in a column. either way, you can structure this data if you take your transactional data and join it to a date dimension table. You can do that in a dataset view…

did you get the issue resolved? how many rows are your dataset and are you using COUNT DISTINCT? ? if you filter to less data or simpler beast modes, does it work?

you don't necessarily have to hardcode the ratios into the data. In fact. Don't do that, it makes it so your card doesn't respond to filters. instead use nested case statements instead of .. concat( case when round(SUM(`This Year` - `Last Year`) / SUM(`Last Year`) * 100, 0) > 0 then '<div><span style="color: green">' ...…

https://knowledge.domo.com/Connect/Personalized_Data_Permissions_(PDP)/PDP_Policy_Autocreation i use this tool for generating pdp policies.

... in excel you're defining a calculation for period over period column... even though you don't call it a beast mode, you ARE defining a formula... BUT that said, the Sparkline chart does give you that ability. If you must do it in a table card you'll have to build a beast mode.

sorry if i'm not understanding. is the data actually uploaded into Domo that you want to see? (i.e. that has a start date outside of the range of data visible in the card)? if so, then ... can you just remove a date filter?

maybe try uploading a subset of the data? do you have clean key-value pairs, or do your values occasionally contain arrays? 

as @GrantSmith said, try Workbench. If Azure DW supports an ODBC driver you should be able to round trip the data from Azure, through your Client machine into Domo via WB. It's not ideal, but it gets the job done and it supports UPSERT. That said, contact your AE / CSM, Azure DW seems pretty common and it may already be on…

a <br> tag is just a line break. so ... recreate the beast mode without the line break.

a fusion on that many rows will work fine. We've used this solution on billion-row datasets. It looks like your data has a lot of dimensional columns already. Using a date dimension, you could compress the width of your fact table and build a framework that introduces a degree of flexibility with 65 columns of low…