Best Of
Solution: Beast Mode & Pivot Table Aggregation Error
Use Case:
This chart uses a Beast Mode code for the Rows and Values in a pivot table chart. The results provide an error that is hard to understand without knowing what is causing the issue. Based on the results, we can also see the Cases is not calculating as expected.
The dataset only has 5 rows of data.
The Rows column in the pivot table is using a custom Beast Mode code.
CASE
WHENMaterial Number= '6' THEN 'Cases'
ELSE 'Gallons'
END
The Values values column in the pivot table is also using a custom Beast Mode code. The CASE statement has multiple FIXED functions which causes the card to aggregate to the dimensions in the card and not the Row Group column as intended.
CASE
WHENMaterial Number= '6'
THEN SUM(SUM(Cases)) FIXED(FILTER DENYMaterial Number)
ELSE SUM(SUM(Gallons)) FIXED(FILTER DENYMaterial Number)
END
The error message also had a hint at the same problem. It states that "Multiple results encountered for the same location". When we change the card type to a table card, it appears that the Gallons section is not aggregating up.
Adding the Material Number helps us see that the card is aggregating, and confirms the card is not using the Row Group column to aggregate. This causes the card to aggregate to the dimensions in the dataset, in this case the card is using the Material Number to aggregate to.
To correct the card and display the correct values we need to do 2 things.
- Correct the case statement logic to allow the FIXED function to use the custom Beast Mode as the default aggregation grouping. This is done by wrapping the case statement with the FIXED function instead of having multiple FIXED functions.
- Add the BY condition to the FIXED function to set the aggregation grouping. Now that the same custom grouping is added to the code that grouping, the card will default to this grouping when aggregating.
SUM(SUM(
CASE
WHENMaterial Number= '6' THENCases
ELSEGallons
END)) FIXED(BY (CASE WHENMaterial Number= '6' THEN 'Cases' ELSE 'Gallons' END) FILTER DENYMaterial Number)
The new code now provides the proper results and aggregates on the custom Row Group column.
JedP
Videos if you need to learn how to Domo
If you ever need to learn how to Domo, I have a full course from Intros to Domo, Data Science, Bricks, etc:
Happy to help
Demo Slam: Dataset Access Requests
Hi All! Hope you enjoyed my demo in the first ever Domo Demo Slam today. Here is a link to it if you missed out:
As promised, here is the step-by-step write up to replicate what I've done in my instance. Enjoy! P.S. this is version 1, version 2 will include more data exploration options for end users.
1. Set up the Domo Governance Datasets connector:
2. Choose the 'Datasets' report from the Domo Governance connector available reports
3. Create a new dataset using the DataSet via Email Connector: and choose the following settings:
4. Build a table card with the following fields (or more, up to you!): Name, Description, Owner Name, etc… In my demo there is a field called "System" that we have created using naming conventions for our datasets. This could also be accomplished using dataset tagging. There are a few fields available in the connector such as: Type, Display Processing Type, and Data Provider ProcessingType that could be used as well. Create quick filters for the Name and Description so end users can search for terms related to the data they want. NOTE: following strict naming and dataset description processes will ensure this works efficiently.
5. Create a beastmode that will be used for end users to click to request access to the dataset:
CONCAT('<a href="mailto:emailAddressFromYourEmailConnectorInStep3?subject=New%20Dataset%20Access%20Request:%20',`Name`,'&body=Access%20has%20been%20requested%20to%20',`Name`,'. Here is a link to the dataset: ',
' https://yourCompany.domo.com/datasources/',`Dataset ID`,'/details/overview . Please share the dataset with the sender of this email.','"></a>Request Access')
6. Add the above beastmode to your table card. When an end user clicks the link for the beastmode in the table card it will pre-populate an email requesting access to the dataset. When they send the email, a row will be added to the dataset you created in step 3.
7. Use a dataset alert to notify you when new requests have been submitted. Each request will create a row in the dataset from step 3. You can create a dataset alert to let you know anytime a row is added:
8. OPTIONAL: use a writeback connector to push the requests into Smartsheet, Salesforce, etc…
Here is what your end result card will look like:
And there it is! Let me know if you have any questions or feedback!
Re: Pivot table total and subtotals are not the sum but the aggregation function
With the default aggregations this won't be possible but you could achieve this behavior by using a beast mode instead. Here's an example where the first column is done by using AVG as the aggregation, while the second is done using the beast mode.
Keep in mind that this works fine with a 2 level row aggregation and you might need to modify it if you're using column breakdown, so make sure you include all the different levels of breakdown as part of the partition clause:
SUM(AVG(`field`)) OVER (PARTITION BY `breakdown1`, `breakdown2`)
So for example, if my Pivot table has the following
Rows: Department, Employee
Columns: Line
Values: Sales
Then the beast mode would be:
SUM(AVG(`Sales`)) OVER (PARTITION BY `Employee`, `Line`)
Hope this helps
Re: Notification of DataFlow and DataSet Processing Failures
I agree with Jae, try building a new dataflow and then add something in the tiles that will force it to fail that way you have some data to play around with.
Right now I have a card setup called "Non Successful dataflows" which filters on Last Execution State = Failed and run state != to disabled (I track disabled flows separately).
My filter also has Last Execution State = Failed_data_flow, Killed, Skipped, and Rejected but I don't have any of those right now so not sure if they are still used.
If you don't have any failed now it may also be a good idea to filter when its not successful or running so when a new category does show up you can check it out. Then you can add a summary number to it and set an alert for when the summary number increases.
Ashleigh