MichelleH Coach

Congrats @colemenwilson! Also shout out to @GrantSmith for reaching 10,000 points on the Community Forum!

@NathanDorsch It might also be worth copying the top and bottom flows from your screenshot into two separate test dataflows. If one runs successfully and the other doesn't that could help narrow down where the issue is. If they both run successfully, try reverting to a previous version of the original dataflow and re-do…

@nmizzell A beast mode calculation would be your best bet since the calculation will update based on whatever filters are selected. The formula should look something like this: sum(`Revenue`)/count(distinct `Date`)

@ScottLeeds To count the whether the completion date was 7-14 days ago, you can add a second condition to your case statement like this: case when `Completion Date` >= CURRENT_DATE() - 14 AND `Completion Date` < CURRENT_DATE() - 7 then 1 else 0 end

@SaskiaV Add a formula tile to the beginning of your dataflow to only accept values that are not equal to ERROR 42 like this: case when `Field` <> 'ERROR 42' then `Field` end

@ScottLeeds Yes, you can use a rolling 7 days approach like this: case when `Completion Date` >= CURRENT_DATE() - 7 then 1 else 0 end

@Mickey You can hide the "Grand Total" label by adding "<br>" in the Label under "Total Row"

@ScottLeeds You can create a numeric field that you can sum through either a beast mode or ETL formula tile like this: case when `Completion Date` >= '2023-06-12' and `Completion Date` <= '2023-06-16' then 1 else 0 end

@damen The Lag function should be used to add a column to hold the prior quarter expenses. In your first screenshot, name your new column "Previous Quarter Expenses" (or something similar), change the function drop-down to "Lag", run the function on your current quarter expense column, with an offset of 1. Once you've…

@renee12345 You can use a Group by tile on the data that includes Name to find the First Non-Null value for Name by ID. Then join that to the data without name on ID to fill in the Name column before appending.

@damen When you use the Rank and Tile column to find the expenses from the previous quarter, be sure to partition by project to make sure you are only including previous quarter data for the same project.

@Rupak I second @colemenwilson's recommendation of recreating the fields. I would do this in MagicETL using the Formula Tile. If you don't want to specify every value to swap, you could use a regular expression to match patterns based on whether `Number` contains alpha characters and `City` contains numeric characters.…

@NateBI It looks like you're on the right track. You should be able to add a color rule to your card based on the Rank field to add a color when Rank is greater than or equal to 8.

@muahmmad_zaidi It looks like @marcel_luthi and @jguzman answered a similar question to this earlier this week. Take a look and see if this applies to what you're looking for

@AZaiter Not all instances allow window functions in beast modes. If you have not used them successfully before, then I'd suggest reaching out to your Domo account team to confirm and get them enabled.

@AZaiter Just to confirm, do you have beast mode window function enabled in your instance and have they worked for you before? Also, you will need to add the outer SUM() back to your formula since that is required for window functions in beast mode.

@AZaiter That helps to know that you want to graph by month. I suspect the issue has to do with the date granularities of your calculation and the groupings in your card. Try removing the partition portion of your beast mode, since graphing by month makes partitioning by month obsolete.

@AZaiter How are you trying to present this metric in your card?

@AZaiter It looks like you're running into the issue because you are partitioning by year. So effectively it is taking the sum over the last 12 years, not months. Try partitioning and ordering by the last day of the month like this: SUM(SUM(fieldsAccident Count)) OVER ( PARTITION BY LAST_DAY(fieldsMonth Ending Date) ORDER…

@User_32667 Possibly, I'd suggest reaching out to Support to confirm.

@User_32667 I just found the same thing on one of my cards, but was able to get the control to return by re-saving the card. Does that work for you?

@User_32667 Can you please share some screenshots of what you're seeing?

@LJ You can use a beast mode like this to filter to the times you want: case when DATE(`Alarm Received Store Time`) = CURRENT_DATE() - 1 and HOUR(`Alarm Received Store Time`) >= 6 then 'Include' when DATE(`Alarm Received Store Time`) = CURRENT_DATE() and HOUR(`Alarm Received Store Time`) < 6 then 'Include' else 'Exclude'…

@bstephan Yes, that's correct. I'd suggest creating a branch of your ETL that uses a formula tile to move the dates forward by 1 day. DATE_ADD(`DateField`,interval 1 year) Then you can rename the `Sessions` field of this branch `PY Sessions`. From there, use an Append Rows tile to merge that with the original data. Once…

@muahmmad_zaidi You will need to use another dataflow that combines the outputs of all 3 existing dataflows into a single dataset to power the Single Value card. I'd suggest using a Group by tile to replicate the summary number from each summary number, then join all the values together and use a formula tile to calculate…

@bstephan With this setup, changing the global date filter to Month to Date would exclude all of last year's data. The best way to make the YoY variances to dynamically update with the global date filters is to restructure your data in a dataflow to add a separate dataset column for prior year sessions. Then you could…

@bstephan I agree with @colemenwilson about using a beast mode column to show the % difference. Mega Table cards are actually better suited than Pivot Tables for showing variances, so you will need to use beast modes to re-create your TY and PY columns and add the variances. Current Year: SUM(case when `Year` =…

@AdamC I agree with the above recommendations to use a variable. In this case, I suggest creating a numerical variable to represent the number of days you want to compare. Then your percentage beast mode would look like this: sum(case when `Days` <= `DaysVariable` then `Shipments` else 0 end)/sum(`Shipments`) So if a user…

@emmanuel_fabre Can you please explain the business logic of the calculation? I see some opportunity to simplify your original beast mode in a way that could resolve the errors, but need more details to be able to provide input

@kav_100 You will need to include the following to the end of your query: WHERE YEAR(`DateField`) >= YEAR(CURRENT_DATE()) - 3