Best Of

Re: QoQ Comparison By Week

You can use the QUARTER function to create a beast mode for your series like this:

QUARTER('datefield')

You can create another beast mode so that the x-axis always shows 1 - 13-ish for each of the quarters with this:

WEEKOFYEAR(`datefield`) - ((QUARTER(`datefield`)-1)*13)

Having said that, you can use period over period charts and change the date range filter to compare to previous quarters. You may want to look into that and see if it can create the visualization you are going for.

MarkSnodgrass

1

Re: Beast Mode Calculation for Yesterday

Hi @user069636

Whenever I typically need to do some period over period comparisons I actually restructure my data so that I have a report date and a "comparison date" (the actual date the metric was recorded on) and calculate it as necessary. This allows you to have custom defined periods (like 2 weeks ago and a week ago).

You'd essentially use the report date in your graph but then have beast modes to determine which type of period to display (last week, 2 weeks ago etc.) and centralizes this logic so you have one place to maintain it rather than across multiple cards and beast modes.

I've written about this method previously here: https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313

Regarding your actual beast mode -

Is your Date field a Date or a Timestamp?

Have you just put in CURDATE() - 1 to see what date it returns to make sure it's returning what you expect?

When I have issues with my beast mode utilize a table card and break down each element of the beast mode into their own beast mode to make sure each step is what I'm expecting. In your case Date in one column and CURDATE() - 1 as a separate beast mode and make sure they line up to what you're expecting.

GrantSmith

2

Re: Help editing a SharePoint Connector setting

If you are the owner of the account, you can go to the data center and then click on Accounts on the left. Then find the account you want to rename and click on the wrench on the right and choose Edit Account. This will let you rename the account.

MarkSnodgrass

1

Re: Creating a Beast Mode for a Tooltip to Compare Data from one Column to Another

Hi @User_32612

You could utilize a window function for your denominator (may need to talk with your CSM to turn this on in your instance):

SUM(SUM(case when `Week` = 42 then

case when `Year` = 2020 then `Dollars Total` else 0 end

else 0 end)) OVER ()

However if Prime Day ever shift again in the future (it will) you have to keep changing the beast mode every year.

I'd rather recommend utilizing a Date Dimension table (you can utilize Domo Dimensions connector -> Calendar Dates dataset as a starting point). You can use a web form to define the dates and if they are a prime day or not, left join that to your date dimension so you have the flag for each date if it's a prime day or not.

You can also define different offsets (last week, last month, last year) which would calculate a given date X days/years/months etc ago for each date.

You can also join your Prime Day dates to each other based on the year and which prime day number so you can have this year and last year's prime day dates on the same row for comparison.

@jaeW_at_Onyx has a good video on this topic you can find here: https://www.youtube.com/watch?v=CDKNOmKClms&t=176s

GrantSmith

1

Re: Beast Mode - Count Case When with multiple criteria

Have you tried breaking down your case statement into multiple beast modes to help diagnose which portion of your beast mode is having issues?

Also just looking at your code you need the question mark to be surrounded by quotes:

(CASE WHEN (COUNT(DISTINCT `ReportDate`)) >= 1 and (COUNT(DISTINCT `Agents`)) >= 1 THEN '1' ELSE '?' END)

GrantSmith

1

Re: Beast Mode - Count Case When with multiple criteria

I might be able to make better recommendations if you provided a sample of your data, but it is likely giving an error because of the quotes around 1 after your greater than or equal to. Try this for starters:

(CASE WHEN (COUNT(DISTINCT `ReportDate`)) >= 1 and (COUNT(DISTINCT `Agents`)) >= 1 THEN '1' ELSE ? END)

MarkSnodgrass

1

Re: Beastmode suggestion for a filter

In the analyzer click on "Add Calculated Field" in the bottom left and write the following formula:

CASE WHEN 'bRentable' = 0 THEN 'Unrentable'

WHEN 'bRentable' = 1 THEN 'Rentable'

ELSE 'Unknown'

END

I used the else clause in case you have empty or null values and didn't want to assume it was rentable or not.

MarkSnodgrass

2

Re: Rank and Windowing functions in beast mode

@dacorson I have had the same experience myself when converting Redshift dataflows to Magic ETL and have had to be very mindful of my naming conventions because Analyzer is case sensitive. It would be a nice enhancement if Analyzer did not care what case a field was when you changed datasets. I would recommend submitting it in the Ideas Exchange section of the Dojo.

MarkSnodgrass

1

Re: Its showing me "Live updates are not available" as the status, how do I solve it?

Whomever is logged into the workbench server first gets the updates, any other users don't but will see the message you're seeing. You'd need to kill the other workbench processes owned by the other users (either by asking the other users or killing it in Task Manager) and then restart your workbench program to then get the live updates.

GrantSmith

1

Re: Change goal color

Maybe I'm missing something, but isn't this already possible using the parameters under Scale Marker?

Rob_E

3