david_cunningham Coach

Thanks @anna.yardley! It's been wonderful to collaborate and learn with everyone. We really have a great and welcoming community 😁 @Grant.Stowell & @ArborRose appreciate the kind words!

@AO3 If you're looking to generate text dynamically. I'd recommend a beast mode that generates the text at an aggregate level based on whatever conditions generate your desired output text. CASE WHEN SUM(TransactionNumber) > 15000 then 'Good Morning' else 'Good Night' end It's important to do the aggregation at the same…

This is a great idea - 100% agree. Thanks for posting!

I don't know of a way to password protect scheduled reports that are sent via email. If you are talking about PPT or PDF exports, you could always encrypt and password protect those files. In general, I would say the best approach would be to not include PII in the display of the cards powering scheduled reports. Meaning…

By trailing white space I mean something like this. "BOCA DEL RAY" vs "BOCA DEL RAY " In any case though, it looks like the reason is that your beast mode is calculating a percentage, but you're not aggregating that percentage. See how you have 2 different values for BOCA DEL RAY You'll need to either wrap your beast mode…

Are you absolutely certain those 2 rows are the same? I’ve had this happen before where 1 had a trailing white space. Also, not sure what the logic is for your Open Capacity beast mode, but make sure it is aggregating.

You'll need to aggregate your raw data up by month/year in an ETL and then do a join on month/year and whatever your goal descriptor is. Another option is to use a date table and a cross join with your webform to generate a date for each monthly goal, divide the monthly total by the number of days in the month to get your…

@AO3 An option for you if you want to avoid FIXED or WINDOW functions. You can always modify to fit your design needs. Use a pivot table grouped by week for the weekly values and the the total. Then you can calculate the average weekly value using a beast mode like this SUM(value) / COUNT(DISTINCT…

@Isaiah02 if you do want the percent of total by sale_type in your table, here is an example of a beast mode you could use to achieve this. SUM(COUNT(Sale_Type)) FIXED (BY Sale_Type) / SUM(COUNT(Sale_Type)) FIXED() Example output

@SwapnaE you can use COUNT DISTINCT with a fixed function. To not allow any filters, you would do something like this. COUNT(DISTINCT `id`) FIXED (FILTER NONE)

With the Google sheets connector, you have to specify which sheet you want to upload, so unfortunately there isn't a built in way to do this. Here is what I would recommend Assuming these sheets all have the same columns. Which I'm guessing is the case since you're wanting to stack them. Combine all your sheets together in…

Good idea @DataMaven! Triggering off of the max week changing would be a good solution. @pauljames you would calculate this column in your ETL as well.

You would want to use a Rank & Window tile in the ETL to assign the most recent data a value of 1. This will be used to filter in a beast mode. Say we call this MOST_RECENT_FLAG You could then create a beast mode in the card, and filter to where most_recent_flag = 'Most Recent Data' case when most_recent_flag = 1 then…

@SwapnaE you can use FIXED functions to achieve this in your denominator. Say your filter column is called ColumnB, and you want to allow a date column (DATE) to filter COUNT(ColumnB) FIXED (FILTER ALLOW DATE)) You can set which columns are allowed to impact the denominator. This will prevent all columns except for what…

@mberkeley you can use the "Multi-value columns" comparative card. Then use the first part of the previous beast mode to calculate the current value AVG(case when most_recent_flag = 1 then value end) And the second part to calculate the comparison value AVG(case when date =…

This is great to know! Thanks for sharing 😎

I would recommend setting up an ETL with a file upload dataset as an input. You can then in this ETL use the "Add Formula" tile to grab the current date. Once you execute the ETL once, you can go back in and use the output as an input and append to create a recursive data flow. For example. Then you'll be able to load the…

@mberkeley my suggested approach would be… Step 1: In your ETL, use the rank and window tile to assign the most recent data a value of 1. This will be used to filter in a beast mode. Say we call this MOST_RECENT_FLAG Step 2: Create your beast mode. You can set your comparison date by adjusting the values in the STR_TO_DATE…

@Julianna_Potter there is a configuration option in the output dataset that let's you specify which partitions you want to keep. Exercise caution: If a partition filter expression is specified, all partitions are evaluated against it and any that do not pass are deleted. If you're talking about on the input side, you can…

@Prathmesh24_Diacto it was great to meet you on our call 😁 please reach out if you would like to work together on anything else. I'm always happy to collaborate!

You can do COUNT(DISTINCT id) @EWG IT Corporate Your example data Beast Mode Result

COUNT(DISTINCT Gender) You don't have to have it in a case statement. If this answers your question, please 'like' and 'accept' it as the answer 😁

@Surya_swaminath these are good references. You have 2 main options Customize an SVG map of Mexico by adding grouping tags, as discussed in detail in Custom Charts. This requires a rudimentary knowledge of XML. Use the Custom Regions tool to upload a DataSet that includes a column for the new custom regions matched with…

Try removing the TIMESTAMP() function from your conversion. A timestamp is a moment in time, with precision down to the microsecond, in no particular time zone. Therefore, it alone does not refer to a specific hour, minute, or second, or even year, month, or day. However, a timestamp may still be treated as if it had…

@Prathmesh24_Diacto - I just completed this write up for a similar question. I think you'll find it useful. I've adjusted it to fit your use-case. This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all…

You will want to use a window function. Here is a breakdown (key step bolded). Step 1: Define the sort order for your steps. You'll use this to sort your table to make sure that it follows a consistent order. For example… CASE WHEN `STAGE = 'Offer' then 0 WHEN `STAGE = '2nd Interview' then 1 WHEN `STAGE = '1st Interview'…

I wanted to write up a more consolidated and easy to follow answer for anyone else curious about how to do this. This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all data. As well as allows users to…

@pauljames - I edited my message to include more context. I tried to post a video, but looks like I'm not allowed to. Feel free to message me directly and we can hop on a call for me to walk you through this.

@pauljames - that beast mode is included as an image above, but also pasting it below for your reference. case when close_date >= DATE_SUB(START_DATE,interval 28 day) and close_date < START_DATE then 'Include' else 'Exclude' end START_DATE is what I called the variable, and close_date is the dates you're filtering on with…

@pauljames - you can use variables to accomplish this. So you would set up a variable that you could put on a dashboard as a control. Then you would create a beast mode that references this variable (above) You can see that above our data currently starts at 1/30 and goes back 28 days. If I switch the date to 2/29, you can…