MarkSnodgrass Coach

@damen what is your date range filter set to? do you have any other filters in place?

Try doing this as a different way to count without using the count aggregation inside the sum aggregation. SUM(CASE WHEN YEAR(`time_stamp`) = YEAR(CURRENT_DATE()) THEN 1 ELSE 0 END) / SUM(CASE WHEN YEAR(`time_stamp`) = YEAR(CURRENT_DATE())-1 THEN 1 ELSE 0 END)

I feel like it is a bug that it does this and could be worth reporting to Domo. The workaround I have found is that Domo will use the last date field added to your table card as the date range field. So, if the field that you want to use as your date range is not being used, remove it from your table card and then add it…

You can create a beast mode that looks like this: CASE when `datefield` >= CURRENT_DATE() AND `datefield` <= LAST_DAY(CURRENT_DATE()) THEN 'Yes' ELSE 'No' END This should work for you.

For the OR statement, you need to restate the field you are comparing against like this: WHEN `ComplaintDesc` LIKE '%ICE%' OR `ComplaintDesc` LIKE '% ICE%' THEN 'ICE'

Depending on what other date fields are in your mega table, you can also use the date range filter and switch the graph by to month and it will format the date fields automatically for you. It depends on what look you are going for if that option would work for you. Just wanted to make sure you are aware of that option.

I often use the LAST_DAY() function to group dates by month and year. It will move all the dates in the month to the last day of the month so then it becomes easier to aggregate.

@Pello the period over period charts are pretty limited in customizing, and you can't customize the legend labels. I would suggest using the line+bar chart and using the beast mode formulas to create your custom series names that would be used in the legend. I created a video that may help you.

@SaloniShah A couple things to point out here: Your x-axis is not being recognized as a date field. When it isn't a date field, Domo will not let you use the "graph by" option in the date range filter. I see your x-axis field is "created date", but your formula in your ETL screenshot is creating a field called…

In your Data Label Settings, set Show Datalabels On to Lines Only.

You can keep your y-axis as count, but change your chart type to the 100% stacked bar. It is found under the vertical bar chart type. This will get your items to add up to a 100%.

Agree with @damen that there is not a perfect solution since you have to assume a single first name and single last name, but here are some things you can do to get close if you can't fix the source data. Leveraging the formula tile in Magic ETL, you can create formula fields and then reference within that same tile. Here…

You could utilize the unix_timestamp function which will convert your timestamp columns into the number of seconds since 1970. This will make both fields integers, so you can do basic math to subtract the two to find the number of seconds and then divide by 60 to get the number of minutes. You can then use that number…

If you have the batch_last_run date as a column in your dataset that stamps when Domo last ran it, you could use that field. You could also use MAX(`datefield`) OVER() , but this only works if you have the "window functions in beast modes" feature turned on. Your CSM can turn it on for you, if it isn't. You also might…

I think your easiest option is to join your data with the calendar dataset in the Domo Dimensions calendar. If you haven't used this dataset, it is very useful and contains information about every date for future and past dates. I would filter the calendar dataset to where dayofweek = 1 so that you get one entry per week…

If I understand you correctly, you want to always show the data where the date is between 8 and 16 days ago and not have to update any logic as your dataset gets new data. You can do this by creating a beast mode and use the datediff function to determine if the data is within your range. It would look like this: CASE WHEN…

Try moving the SUM to the outside of your CASE statement instead of being inside. I would also double-check your data to make sure you are matching exactly, such as case-sensitivity and extra spaces.

Do you want to sum them separately and then add them together? Or do you want them as 3 separate calculations? If you want them as 3 separate calculations, then you would create 3 different beast modes, one for each. It may be easiest if you explain what it is you are trying to achieve. What are you trying to display?

Try this: SUM(CASE WHEN `Data` in ('Goal','Pipeline','in P&L') THEN `Total` ELSE 0 END) This should get the total you want.

I would not be surprised if @Ellibot has done something like this.

Try this: sum(case when `lgract` = '4001' then IFNULL(`crdamt`,0) end ) - sum(case when `lgract` = '4001' then `dbtamt`end )

You need to make 8 different beast modes, 1 for each line you have in your screenshot. Then drag those 8 beast modes into your table card.

@ageibe You can do this with the Group By tile in Magic ETL. In section #1 (select what columns identify the grouping) put your name and patient Id columns in that section. In section #2 enter the name you want to give the most recent date column In section #3 choose the encounter date column and then choose maximum for…

I would suggest doing this in Magic ETL. You can do this with just a few tiles, the formula tile and the join tile. In the formula tile, create a field called LastYearDate and use the date_sub function to subtract one year from the date field in your datasest. You can then use the join tile to join the data back to itself…

@damen I have run into issues where I have a lot of historical data and if the date range filter is set to graph by year, it does not go all the way back. Support once told me that it has to do with their calendar system behind the scenes. I was able to get around it by creating a beast mode that extracts the year from the…

@WorldWarHulk My bad. I missed that you are looking at 2 different dates to determine the quarter. It will involve a case statement, it can still be dynamic. This should work for you: CASE /*check to see if the end date is in the previous quarter of the same year */ WHEN QUARTER(`TC: Created Date`) - QUARTER(`TC: End…

@mroker LOL! I wish you could hit awesome 10 times! Glad it helped you and will hopefully help others.

You are going to want to utilize the Domo Dimensions Connector and the Calendar dataset to build out your months that are in between your start and end dates to create new rows. You would do this in Magic ETL. I've created a video that shows you how to do it. This is what your ETL would look like. Hope this helps.

Put the start date beast mode I gave you in the x-axis and put either the service field or the account field in the y-axis and then choose the aggregation type of count if it doesn't automatically do it for you. That would give you a line chart by start date.

Depending on how your data is structured, it seems like you could dress up a table card or pivot table card to get the columns you want.