RobSomers Coach

Depending on how your data is organized, you should be able to do this within the card itself. If you use the date as a column/axis, then you can just do sums of all the columns you add and Domo will do all the grouping for you. This will also let you do all the custom filtering and timeframes you want.

Are you using the QuickBooks Online Reports Connector? It shows that you can pull the following datasets, and I would guess the summary one is what you're after: Alternatively, depending on if you can identify parent and child accounts with how your data is coming over, you could manually create this in a card using a…

@Jones01 Make sure to mark my post as accepted so that this thread gets marked as solved!

I think you can do this within a card by creating a beast mode using the DAYNAME() function with your date column that you can use as a column or your x-axis. Then the average for each day of the week would be another beast mode: SUM('amount_column')/COUNT(DISTINCT DATE('datetime'))

Another way of doing a beast mode to rename null values is: IFNULL(date_column,'No Date')

Can you do the concat if you rename one of the columns in the data view?

Once you have your output dataset, that dataset is the table, so there's no way to reference the source table it comes from. If you've done the join in MagicETL, then you should have the option in the join tile to drop duplicate columns. Alternatively, you could just rename the duplicate columns.

If you were doing only one bar each year, you could use a Bullet chart which lets you use a beast mode to set a goal. For how you want to set it up, my thought is to do either a line and bar chart or a symbol and bar chart and use a beast mode to calculate your major and minor goal lines for you lines/symbols.

Are your 0's in your data actually 0 or are they null (Ex: row 'a' column '2000' in your example). If they are null then you will need to do IFNULL('Column',0) for each of your columns in the beast mode, or use your ETL to change the nulls to 0. This is the only thing I can think of at the moment.

Could you post your beast mode?

I don't think there's a way to do this, which seems a little odd. I'd use 'Feedback' to suggest it to Domo as well as post it in Ideas Exchange. I'll definitely upvote it if you do!

It's going to be a little tricky, because doing it as a pivot table, your beast mode is basically going to be based off of the dataset and affecting that, and not the pivot table directly. If you're fine with the renaming the Grand Total Column to be your Collection Total column, and you're ok with the '8000' column…

So you've done the pivot in a Magic ETL and the pivot is the output and what the formula needs to be based on?

You can do a beast mode for Collection Total something like this: case when `Class` = 8000 then `Sum of Net`*-1 else `Sum of Net` end Then you can just do a sum of the Collection Total column when you add it to your table.

In the flow you can use a rank and window tile to do a row count for each project: https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate Then for each column you want the duplicates to be null, you can do a formula that says when the row number is not 1 then the value should be null.

@andres Are you using the same date field for your Minimum Value and Maximum Value?

You can check out the Domo Dimension Connector. It has a few country files that you could check out and see if they would work for what you need. https://domohelp.domo.com/hc/en-us/articles/360042931454-Domo-Dimensions-Connector

If you want to got the bar and line route, I think you'll need to use a new date column to graph by. Right now, your data is looking something like this with the beast modes: Date Inventory This Year Last Year Wk 1 2022 1,000 sno Wk 1 2021 1,000 sno You've identified last year and this year, but last year will still be…

Not 100% sure on how to fix the sorting when you don't have the column, but if you just don't want the column to show on the card, you can just hide it using Hide Columns under General chart properties.

Because of the way case when statements work in a waterfall manner, you won't be able to achieve exactly what you want. It will evaluate the first part and then it will be impossible for anything that fits the first part to go into the other categories. The are ways to do what you want but they get kind of complicated. I…

Are you running this query outside of Domo, or are you using it somewhere in Magic ETL?

Is the problem that you're getting an error, or is the where clause not working as expected?

The 103 count might be counting drill path cards, which won't show up on the list of cards.

It sounds like you're needing to do a join between the royalty reports the metadata. You can use Magic ETL and the Join tile, you can use a left join to attach the metadata to the royalty reports where the track title from the royalty sheet equals the track title from the metadata sheet (assuming track titles are unique).…

If you wanted to go the beast mode route, you're on the right track. You need to create a CASE WHEN statement within the SUM function: SUM(case when 'Divisional Expenses' <> "Marketing" then 'Amount' else 0 end) 'Amount' would be whatever your value column is. Another option is to just do a regular sum of you Divisional…

You could use Magic ETL to group by store and month, and do a sum of Sales and maximum of Monthly Budget (assuming it stays the same the whole month). This would give you an output like this: A May $120 $300 B May $30 $200 Then in your card just use a beast mode to calculate sales as percentage of budget.

For your percentage of patients admitted via observation, you can use a beast mode like the following: SUM(case when 'Enc status' = "Observation" then 1 else 0 end)/COUNT('Enc status') This will get you the total observation patients divided by the total patients. To get it broken out by provider, you can just let Domo do…