Best Of
Re: Fixed Beast Mode for Percent of Category
This wouldn't work for my use case but I did find a solution by wrapping the denominator with the fixed by function and adding the date being used as the filter on the card to the fixed by statement:
COUNT(DISTINCT CASE WHEN Status = 'Unqualified' THEN Lead ID END) / (SUM(COUNT(DISTINCT Lead ID) FIXED(BY Company, Lead.CreatedDate)))
lb1234
Re: Can you do a running total in a pivot table?
@randall_white there is a feature in Domo tables to display as running total in the ‘Show Values As' menu of a column. I have tested this out in Pivot Tables, and it seems to work vertically, but not horizontally..
For example, my pivot table below will sum up revenue in running total for all rows in September column…but it doesn't roll up horizontally across columns from Sept/Oct. This feature may be a solution in some cases, but not all.
The solution I found to sum up across columns….is using a window function beastmode, for example, if I want to sum the revenue column across time.
I plug that beastmode into my values column and can see that it's totalling up across columns, but not grouping how I want, which indicates I need to add a PARTITION BY clause to the grouping elements in the pivot rows
Let's update that formula
And visualize the result, which is looking much better because it's grouping the running totals by the row groupings in my chart (department,sales_rep) and ordering it by date.
Re: Beast Mode Combining Columns
@Stucker Try removing the CONCAT functions in your formula since that will prevent your calculation from seeing your fields as a number. Also, do any of your 6 columns have null values? If so, you will need to wrap each field in an IFNULL(`fieldname`,0) since adding nulls across the same row will cause values for the entire row to return null
Re: How can I group these results by date?
Hello @MichelleH @RobSomers @ArborRose, thank you so much for all of your inputs. It's working now. I just used the date() function and it worked perfectly!
Zel
Re: How can I group these results by date?
@Zel Is your date column just dates or is it datetime? If it's datetime it could look like the same date but they're actually unique because they have different times in the background. If it is a datetime column, try using a DATE() function on it and see if that works.
Re: What are the est practices to maximize speed and minimize data storage when updating a large dataset
We finally cracked this nut this month.
Here is a big thing I was missing - the datasets created by the CLI tool or Workbench can have many, many partitions. The limit is MUCH higher than 1500. You still don't want to get stupid with them, but there is more room. Magic can only process 1500 partitions. So when you bring in partitioned data, you need to get < 1500 partitions and when you write to partitioned data, you need to write to a dataset with < 1500.
Here is what we did …
- We bring in data every day. We went back and grabbed all of our back data and it is all loaded by date of business and timezone. 1460 partitions in a year. So if I ever needed in magic to parse a full year, I can. But not much more than that. But that's ok.
- To build the data, we use the ability in our dataflow to select only certain partitions, e.g. getting only a year of data to back build. We then output to data partitioned by month (YYYY-MM to be more clear).
- Once the back data is done, we start on our regular processing. Every day when new data comes in we grab all partitions from the current month AND previous month. Why? We can't trust that on April 1st we won't have to re-state the data from March 30th. Best case, we are processing 29 days of data on March 1st. Worst case we are processing 62 days of data on August 31. But that's a-okay. That data is again partitioned out by month (YYYY-MM). So if some weird thing happened where on April 1 we realized we needed to restate 2/28's data, we could manually re-select all of february's data and restate that partition.
And then Bob is our uncle. If we need to re-use the finished datasets downstream, we account for those partitions and carry over the YYYY-MM partitioning. So even on our 3B row dataset, we can process what needs to be processed in minutes.
We thought we would have to take all of our raw input datasets and JUST partition them and use those partitioned datasets downstream, effectively re-stating our data. We ended up not having to do that. Hooray.
A few other roadblocks we thought we had I addressed above.
Going back to how we bring the data in. Here we are dealing with a SOAP XML endpoint that we use a python script to grab, parse, and output. We used to use the python sdk to upload to domo but that doesn't support partitions.
So in python we grab everything, and then call the CLI tool and let the CLI tool do the uploading. We do CLI tool over WB because these are running in linux containers. Master control launches the container, the container does the work, container closes. I make budget, everyone on my team get a bottle of bourbon. Win, win, win.
SeanPT
"Jump" button that scrolls to section of dashboard
Hello, I have a dashboard that has a hefty amount of cards that are organized into sections marked by headers/borders. Is there a feature in Domo that allows me to add a button that users can click to automatically scroll down to a section? And in the same vein, perhaps a "back to top of page" button? This would make navigation simpler for salespeople.
Thank you!
jrtomici