MichelleH Coach

@louiswatson You can use the STR_TO_DATE() function to convert those values to date like this: STR_TO_DATE(`DateField`,'%d/%b/%Y') The second argument of the function uses these date format specifiers to specify what format the existing field uses.

@neilprobst Do you have an aggregation set on the Spend filter in analyzer?

@LeonN Can you please share some more information about the data you already have in Domo? The solution will be different depending on how inventory is measured in your data

@renee12345 Yes, you can use the "Card Fields with Beast Modes" report from the Domo Governance connector. It will tell you which fields are used on each card.

@cmarino The reason it's sorting like that is because the beast mode is being sorted as text and not a number. You could either add a leading 0 to your labels (i.e. 01 Month, 02 Month, etc.) or create a second beast mode with a formula of DATEDIFF(date,published_date) to use as your sort field.

@ColinHaze You can accomplish this using case statements inside your sums. The LAST_DAY function can be used to transform dates to the last day of the month so you can compare months. Current Month CPL sum(case when LAST_DAY(`Date`) = LAST_DAY(CURRENT_DATE()) then `carrier pay` else 0 end) / sum(case when LAST_DAY(`Date`)…

@hunter_brown Unless you want to set up a variable, you will have to select Between December 1, 2021 and December 31, 2022 in the date filters. The rolling date ranges only work relative to the current date.

@hunter_brown You can filter to the last 13 months by selecting Choose Date > Previous > Last and it will give you the option to choose a rolling date range.

@Anna_Yu Try this instead: Y-Axis: Category X-Axis: Year 1 (Beast Mode) Series: Year 2 (Beast Mode)

@Anna_Yu Is the `current_year` field actively used in your chart? Color rules only apply to fields that are also displayed in the chart.

@Anna_Yu You can leave the "is always" condition and the color will apply regardless of the value

@Anna_Yu You will also need to create beast modes for each year selected, like the ones in my original post. Those beast modes are what the color rules will be based off of, so if you named them differently that is what you will need to look for in the drop-downs

@Anna_Yu Do you always want the values to be red and black? If so, you can create two color rules so Year 1 is always red and Year 2 is always black. Here is a KB article on how to set up color rules:

@JasonNgaiGPT The MAX around your beast mode is only returning the option that is last in the alphabet, in this case "Out". Try removing the max to see if that gives you the "In" option.

@Anna_Yu This sounds like a good use case for Variables. I'd suggest creating two variables to select the years you want to compare. Here are some sample beast modes you could use with these variables: Year 1 Total: sum(case when YEAR(`Date`) = `Year 1` then `Amount` else 0 end) Year 2 Total: sum(case when YEAR(`Date`) =…

@ScottLeeds Is there actually a column called id in your dataset? If not, you can change those portions of the beast mode to a different field, as long as it does not contain nulls.

@M_Gonzalez The Calendar dataset from the Domo Dimensions Connector contains a list of all possible dates. I suggest joining Table A to the Calendar dataset then filtering to remove duplicate statuses

@Seth Just to make sure I understand what you're trying to accomplish, what are the % change values you're expecting based on the example data you shared?

@Dheeraj_1996 I agree with using the chart types the others have suggested. Here is a KB article with more details on how to structure your data: You may also want to consider the nested bar chart, which will calculate the total without having to restructure your data:

@Trumpetbob Rather than adding a zero, I would follow @GrantSmith's suggestion and wrap each option in a COALESCE function to force null values to zero: COALESCE( -- B2 (SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END)) * (SUM(CASE…

+1 This is absolutely needed

@Pam1324 Since the only difference between the calculations for Month 10 calculation and all other months is the numerator, the grand total will work more cleanly with the case statement inside the first sum like below. Aggregations work best when they are outside of the case statement, rather than inside them. sum(case…

@Trumpetbob I'd recommend modifying the second portion of each calculation to move the lead level criteria to within each case statement since the case statement not wrapped in a sum is likely causing granularity issues. ( -- B2 (SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / SUM(CASE…

@Trumpetbob What do your calculation and result look like when you try to combine all 5 lead types? When you say it doesn't work, is it returning an error or an incorrect result?

@m_malecka I've run into that issue as well and have not found a way around it. I'd suggest contacting Domo Support about the "Hide Subtotals" option not working and posting to the Ideas Exchange to about enhancements to the subtotal functionality.

@hunter_brown Are there any null values in the credits and debits columns? If one of them is null and the other is not then subtracting will return null, so you may need to replace the nulls with zeros in each individual column.

@hunter_brown Do you have the beast mode filter from your original post applied to the card?

@hunter_brown It looks like you can actually simplify this calculation a bit. I've often found that errors can occur when nesting aggregates. Try something like this: ( sum(case when LAST_DAY(`Posted dt.`) = LAST_DAY(CURRENT_DATE() - 60) then `Credit` - `Debit` else 0 end) - sum(case when LAST_DAY(`Posted dt.`) =…

@hunter_brown In your second formula, try changing the NULLIF functions to IFNULL.

@user14170 I don't know of a shortcut for naming the columns off hand. Do the pivoted columns need to be coded into the dataflow or are you able to use a pivot table card? If you have a large amount of charge codes you need to pivot, then having them listed as individual rows may give you more flexibility downstream.