rco Domo Employee

From your screenshots I'm assuming you have three formulas for each of five rows, that's where the numbers below are coming from. Start with an Input DataSet tile. We'll split that tile's output out to five Add Constants tiles, where we'll add a column called "Row Name" to each, with values "Baseline", "Rejected",…

This will be easier in the near future with the Join tile ON clause and the Series (row generator) tile. For now, the solution to this problem is described here. There's more than one answer; the more complicated one involves generating a series table within the flow, the simpler ones assume you've built a series table as…

You should be able to take the solution we did for Project Count with these changes: 1. Rename "ProjectId Const" tile to "Cost Const", name the constant "Planned Cost" instead of "Project Id", and make it zero instead of negative one. 2. In the Group By, do a Sum of "Planned Cost" instead of a Count Distinct of "Project…

You could add phase totals by adding another branch of the original input with its own Group By, grouping by only Phase and doing a Count Distinct on Project Id called Total Project Count, and then Join that branch back in to your final result using Phase on both sides as the join key.

Previews operate on a limited number of rows from their inputs, by default just 10k rows: This can cause Group By aggregates to look incomplete, Joins to miss when they should hit, and Filters to produce zero rows when they should produce some.

Also, since this is missing from my original comment, here is the configuration for the "Correct for Dummy" Add Formula tile:

The Add Constant that comes right before the Append should add a constant "Project Id" with value -1. The goal is to create a table that has three columns that all correspond to columns in the original table, Requestor Organization, Project Id, and Phase. You've got everything up to the Join correct, that should be giving…

This will give you the maximum/only matching value within each ProjectId. The other solution was for the max/only across the whole dataset. If the value you want is within each unique ProjectId then this is your solution.

I'm referring to the first configuration step of the Group By, which in my Domo version looks like this: Here you really want to put nothing, since you just want to do the MAX() across the entire dataset. But it may not let you put nothing, so we put a constant value 0 instead. i.e. add an Add Constants tile before the…

To be clear, you just want the maximum (or only) value of "Yearly Total RUN Costs (CHF)_p" where BatchTimestamp is November 22nd 2023 to show up on every row of the dataset? You'll have to use a Group By instead of an Add Formula since it's an aggregate function. Depending on your Domo version, it may allow you to specify…

Sure, add an ELSE 0 to the case statement.

What's happening there is pretty weird, I'll try to explain it: When you write a Group By formula, Magic ETL allows you to reference columns both inside an aggregate function (like SUM()) and outside one. When you reference a column inside an aggregate, that reference is evaluated for every row to produce the input to that…

Unfortunately there's currently no way to do this in a way that will dynamically adapt to the actual set of unique Requestor Organizations. If you can tolerate enumerating the Requestor Organizations explicitly, then this solution works: In the "upper" section, all we're doing is creating a cross-join table of all the…

I think you're saying that the case statement matching in the first formula is affecting the second formula somehow. I'm not sure how that could happen; they should be independent of each other. However, I do notice that you don't have a SUM() function wrapping your formulas, and it seems like you would want one. Could…

The issue is the comparison with that string, 'Nov 22, 2023 12:00:00 AM'. That comparison will first convert the left hand side to a string according to the Timestamp Format setting or the default timestamp format, which would be '2023-11-22T00:00:00'. Then it will perform the comparison on the strings, and they won't be…

Thanks for your report. This change in behavior is not intended. You should see it working the way it used to within the hour.

Instead of a large case statement that explicitly assigns a week number to every day in the year, you should be able to express your week logic with some combination of YEARWEEK and INTERVAL arithmetic. I don't have enough information to know if this matches your logic well enough to work for future years, but this…

When the join 'on' expression feature becomes available to you there will be a better way to do this, but for now, here is the best I can come up with: The general strategy is to make a table that contains all the unique values of ORDQTY repeated a number of times equal to that value. Then we will join that table to our…

@MattLynn Be sure to check out the edit to my answer; I was missing a pretty significant optimization/simplification. Let me know if it works out.

Regrettably, window functions aren't supported in Magic ETL formulas, and as you've already determined it's not possible to express that window frame using the Rank & Window tile. Including the frame boundaries in the original data, using Beast Mode, or using the Python Script tile are all options. If the whole input…

@SwapnaE It looks like your upsert key was empty in one of the rows your DataFlow produced. If you replace empty strings in it with some other value, does the issue go away?

I would recommend trying to understand the problem a little better before fixing it in that particular way. The error message you're getting back should include an example of a failing value. Something like "Failed to parse text '$1.23' as type…", or perhaps "Failed to parse text '#VALUE!'…". If we figure out exactly…

There's a button here in the formula editor to expand it: Clicking it shows you this editor, which has a searchable function list and documentation: There's also this support article. Though as of this writing that article does not stay up-to-date automatically with documentation changes or additions, so I'd recommend the…

I'll just add some context for anybody still watching this issue. This comment is specific to Magic ETL; Beast Mode (Domo Adrenaline) works differently: Issues like this happen when you mix floats and decimals, or sometimes integers and decimals (since dividing an integer by an integer produces a float). When a decimal is…

Ideally you'd figure out how to express your week system in terms of one of Magic ETL's built in week modes. Then you can use the WEEK and YEARWEEK functions plus your adjustment to arrive at the year and week you want for any given date. The WEEK and YEARWEEK functions both accept an optional second argument to indicate…

Since there isn't a "string contains" operator in the dashboard filters, I've had to come up with a somewhat more tedious way of accomplishing this. It does involve opening and modifying all the cards individually, but only during the setup; once it's done you can use a single control on the dashboard to do your filtering.…

Use the "Add Formula" button in Group By to write something like this: GROUP_CONCAT(DISTINCT FieldC SEPARATOR ', ') Your "columns which identify the grouping" would just be "Field A". Charlie, yes, we certainly can and should add an option to that drop-down to "Combine unique strings separated by ,". Generally, features…

@Charlie Bantoft It sounds like you want this formula: GROUP_CONCAT(DISTINCT Model SEPARATOR ', ') Use the "Add Formula" button instead of the "Add Column" button in Group By and use that formula in place of the "Combine strings separated by ," aggregations. Don't forget to replace Model with Colour for the other one.…

First, we should solve problem #2. Then, we'll create a canonical lookup table from Number to City to solve problem #1. Use an Add Formula tile to make two new columns, Canonical Number and Canonical City like so: Canonical Number formula: NULLIF(STR_DIGITS(CASE WHEN City BETWEEN '0' AND '999999' THEN City ELSE Number…

Could you clarify the problem you're having with Rank & Window? @ST_-Superman-_ is correct in saying that the Lag function is what you want. A lag of 1 will give you a new column whose values are all offset by 1 from those of its source column. That's not a complete solution, though, since you'll still have nulls in every…