Best Of

Re: Find greatest value for each specific ID

I'd use the Rank and Window Tile

https://domohelp.domo.com/hc/en-us/articles/360042922814-Old-Magic-ETL-Tiles-Rank-and-Window

and then partition by your ID.

trafalger

2

Re: What is the best way to calculate?

@damen You should be able to do a beast mode like the following (using 'Product' for your 1st Home or Home Again column):

AVG(case when 'Product' = '1st Home' then 'spread' end) - AVG(case when 'Product' = 'Home Again' then 'spread' end)

If you want to keep your chart as is and have the difference as another bar, you'll need to do separate beast mode calculations for the spread of 1st Home and the spread of Home Again, then add those and the difference beast mode to the chart (same goes for doing a bar and line chart).

RobSomers

1

Re: Splitting by delimiter vs LOCATE() function equivalent

use COALESCE() function

jaeW_at_Onyx

2

Re: Splitting by delimiter vs LOCATE() function equivalent

INSTR() will return the location of the first instance of a string

ST_Superman

2

Re: Success rate calculation

@louiswatson If you do the beast mode for each percentage in a card, this becomes a lot simpler because you can do:

SUM(case when 'Type' = 'Approved' then 'Approved or Refused' end)/SUM('Type')

And do the same for Refused and Other. If you want to do it in the ETL and do a separate column for each success rate, then you can use the formula above to do the same thing. You only need to use the FIXED function if you want to do the calculation with a single column in the ETL.

If you want to go the FIXED route, in your formula, you don't need anything in the parentheses after fixed because you're interested in the sum of the whole dataset. So your formula would look something like this:

SUM('Approved or Refused')/SUM(SUM('Approved or Refused') FIXED())

If you want to ignore 'Other' in your totals, you can use a CASE WHEN statement in the SUM portion of your FIXED function to only count 'Approved' and 'Refused'.

RobSomers

1

Re: Population calculations in beast mode?

AVG(SUM(`headcount`)) FIXED (BY `date`)

That will give you the average daily headcount. You would want to put Date field as your x axis and select the "min" aggregation. Then sort the data by WEEK(`date`)

Or you could use your "graph by" option to change it to week. I prefer using the MIN of the Date field though because it is more readable in my opinion to see the first date of the week rather then Week 15 or Week 26... I don't really know what dates those are at a glance.

ST_Superman

1

Re: Convert Dashboard to Sub-Dashboard?

You can manage this in the Admin section:

ST_Superman

6

Re: Changing Number Formats with a Quick Filter

@Chris_Wolman If you have access to variables (I think it's still in beta), you can use a variable and beast mode to create a toggle to switch between normal values and thousands. If you don't have variables, you can contact your CSM about getting the feature activated.

RobSomers

2