Best Of
Re: Limit on ranking tiles
@dacorson , Domo DataFlows Product Manager here. The only true limits we have in Magic ETL is whether or not we can complete the specified transformation steps in a 24 hour window. There are additional nuances to it but that's the most common limit one may encounter at one point or another when dealing with extremely large amount of data or complex DataFlows.
For added context, the Rank and Window tile is one of the most "costly" actions in Magic ETL. What I mean by "costly" is that it is one of the most processing-time-intensive-actions we have as that action needs to evaluate every row of data against each other row within your ETL (similar to remove duplicates, but even more complex). Without having any additional information, the out of memory error you are experiencing seems normal to me as 2 million rows of data compared against each row of itself 20 times becomes an extremely large processing job very fast. If this DataFlow used to work with those specific transformations and stopped running successfully, or you feel there is a bug/error with Domo, then I encourage submitting a Support case as that will get reviewed and triaged to the appropriate engineering team for evaluation. With a support case and specific dataflow cited, Domo Support and Engineering can investigate further and provide additional help if you would like to go down that route.
Thanks for the ping @GrantSmith
Re: Any chance I can add in a percent change over various fields in a pivot table via window funciton?
@MattLynn I think that you will be best served by splitting this table into multiple tables. One for case load by source, one for Resolution method, and one for Customer Experience.
I'm not sure how your data is structured, but I took a stab at the first table (case load by source). The part that I stumbled on here was that your summary value is the total case volume while the rest of the values are displayed as a percent of that total. I chose to include both on my table:
Here are some of the beastmodes that I wrote, but these may not work for your dataset. It might be easier if you shared a bit of the data behind this request so that someone can try to provide a more concrete solution.
2021 Qty
SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END)
Qty % Change
(SUM(CASE WHEN `Year`=2022 then `Quantity` ELSE 0 END) - SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END)) / SUM(CASE WHEN `Year`=2021 THEN `Quantity` ELSE 0 END)
2021 %
sum(case when `Year`=2021 then `Quantity` END) / sum(sum(case when `Year`=2021 then `Quantity` END)) over ()
% Change
((100*sum(case when `Year`=2022 then `Quantity` else 0 END) / sum(sum(case when `Year`=2022 then `Quantity` else 0 END)) over () ) - (100*sum(case when `Year`=2021 then `Quantity` else 0 END) / sum(sum(case when `Year`=2021 then `Quantity` else 0 END)) over ()) ) / (100*sum(case when `Year`=2021 then `Quantity` else 0 END) / sum(sum(case when `Year`=2021 then `Quantity` else 0 END)) over ())
I'm not sure how helpful this will be, as I probably do not have the same schema as your dataset, but I hope this helps.
Re: Year = current year
Hi @RajNadar
It would help to understand what format `Booking Year Nbr` is in.
CASE WHEN `Booking Year Nbr` = YEAR(CURDATE()) THEN 'CY' WHEN `Booking Year Nbr` = YEAR(CURDATE())-1 THEN 'PY' ELSE 'Other' END
Re: Pivot Table showing correct grand totals but not all records
@dgsinclair - For pivot tables, you can only sort on any fields that you have in the table itself. If you sort on anything else, then you will only see the first value in the table (although the totals will still be accurate).
I believe this is intentional. You may want to highlight the largest deal won in a month, but then still show total sales. You could to this by sorted on deal value descending. You would then see the largest deal closed each month in the table, while the totals would reflect all deals closed.
I think there could be an added message to alert you to this fact anytime that you add a field to the sorting list that isn't in the rows/columns of the pivot table.
Re: Fiscal calendar - select like other date-related filters, bulk enable on cards, apply to beast modes
Absolutely require this functionality for our end users. Ideally also having the ability to switch the type of calendar within the global date selector so it applies to all cards would be great.
Jones01
Re: A more flexible way to do Period over Period comparisons
Addendum
Here's a corrected / updated Magic ETL implementation as the prior version had some incorrect offset values.
{"contentType":"domo/dataflow-actions","data":[{"name":"Domo Dimensions - Calendar","id":"3ea88485-92ab-455d-8e70-118cdd851191","type":"LoadFromVault","gui":{"x":60,"y":18,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"d6b99a99-63c7-46f0-8e54-38045e29e710","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Current","id":"66ec18a6-b8dd-4fff-a67d-7195583d1d24","type":"Constant","gui":{"x":168,"y":18,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Current"}]},{"name":"Current Date","id":"436c9649-202a-4da3-a300-1cdfc37c42db","type":"DateCalculator","gui":{"x":276,"y":18,"color":null,"colorSource":null},"dependsOn":["66ec18a6-b8dd-4fff-a67d-7195583d1d24"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"0","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Week","id":"cf47b30d-dd64-4dc3-8be9-0bc7f157efdd","type":"Constant","gui":{"x":168,"y":114,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Week"}]},{"name":"Last Week Date","id":"25e60073-8cae-4ec4-a85a-e72b19c539c9","type":"DateCalculator","gui":{"x":276,"y":114,"color":null,"colorSource":null},"dependsOn":["cf47b30d-dd64-4dc3-8be9-0bc7f157efdd"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"7","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Month","id":"5231a16c-b884-491e-b644-2a3d65efa9ab","type":"Constant","gui":{"x":168,"y":210,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Month"}]},{"name":"Last Month Date","id":"a24b1efd-4f25-49af-aefb-73db6b554ff6","type":"DateCalculator","gui":{"x":276,"y":216,"color":null,"colorSource":null},"dependsOn":["5231a16c-b884-491e-b644-2a3d65efa9ab"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"1","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"28 Days Ago","id":"a62144ce-10f6-4039-94c6-2e11432f1ef2","type":"Constant","gui":{"x":168,"y":306,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"28 Days Ago"}]},{"name":"28 Days Ago Date","id":"1815477c-0704-4d66-9c21-e48b5c3554be","type":"DateCalculator","gui":{"x":276,"y":306,"color":null,"colorSource":null},"dependsOn":["a62144ce-10f6-4039-94c6-2e11432f1ef2"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"28","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Year","id":"ef158a02-fd86-44d1-99bf-09195c4b4496","type":"Constant","gui":{"x":168,"y":402,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Year"}]},{"name":"Last Year Date","id":"86d4f7ec-1e4e-4e50-91b4-a44099fd2cf0","type":"DateCalculator","gui":{"x":276,"y":402,"color":null,"colorSource":null},"dependsOn":["ef158a02-fd86-44d1-99bf-09195c4b4496"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"12","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"Last Year DOW","id":"9eaae432-7bc6-49d7-b451-81e58f6265bf","type":"Constant","gui":{"x":168,"y":498,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Year DOW"}]},{"name":"Last Year DOW Date","id":"7f79d3cf-718c-4bea-9b19-9a257260fc4d","type":"DateCalculator","gui":{"x":276,"y":498,"color":null,"colorSource":null},"dependsOn":["9eaae432-7bc6-49d7-b451-81e58f6265bf"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"364","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Append Rows","id":"986ae138-d19f-4f35-81c5-26a888471497","type":"UnionAll","gui":{"x":552,"y":228,"color":null,"colorSource":null},"dependsOn":["436c9649-202a-4da3-a300-1cdfc37c42db","25e60073-8cae-4ec4-a85a-e72b19c539c9","a24b1efd-4f25-49af-aefb-73db6b554ff6","1815477c-0704-4d66-9c21-e48b5c3554be","86d4f7ec-1e4e-4e50-91b4-a44099fd2cf0","7f79d3cf-718c-4bea-9b19-9a257260fc4d"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_ALL","strict":false},{"name":"Select Columns","id":"4c0c4d70-c9c5-4d7d-ae89-6bf7278e6abf","type":"SelectValues","gui":{"x":647,"y":228,"color":null,"colorSource":null},"dependsOn":["986ae138-d19f-4f35-81c5-26a888471497"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":"Report Date"},{"name":"Period Type","rename":null},{"name":"Comparison Date","rename":null}]},{"name":"Calendar Dates With Offsets - Magic ETL","id":"1abf7dfe-23c2-4641-8a6d-1744f8d6410f","type":"PublishToVault","gui":{"x":768,"y":228,"color":null,"colorSource":null},"dependsOn":["4c0c4d70-c9c5-4d7d-ae89-6bf7278e6abf"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"f0f185c5-8343-45a2-863b-f1ddd7514ac5","type":"DataFlow","name":"Calendar Dates With Offsets - Magic ETL","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}
Re: Pivot Table showing correct grand totals but not all records
@dgsinclair are you sorting by anything? That will usually mess up totals.
Ashleigh
Re: Finding the sum of values based on a common column, excluding duplicates
You’d need to incorporate a calendar dimension table in an ETL. However because magic ETL doesn’t support conditional joins it gets a little messy. You would need to add a constant to both a Calendar data set and your actual data set and Collett join column and set it to a value of one. Then you would join your calendar Dataset to your actual data set based on just the join column. After the join then you would filter your data such that the calendar date is between your start date and end date. Then you would run a group by based off of your ID and count distinct the different dates . After that you can then join this outfit to your original Dataset based on the id field to get your sum.
alternatively you could go with the MySQL dataflow to do the conditional join with the same calendar Dataset and your actual data set and then also doing a group by on your ID and then doing a count distinct on the dates. It is a little simpler and easier to understand but magic ETL tends to run much faster than MySQL
Re: Including multiple values on on card
Minor edit to @GrantSmith 's suggestion, to re-format the percentage:
CONCAT(SUM(`delivers`), ' delivers ', ROUND((SUM(`delivers`)/SUM(`total`))*100,0), '% delivery rate')
