Best Of

Re: How can I remove line breaks from a string column in an ETL?

You can use a formula tile and the regexp_replace function:

REGEXP_REPLACE(`field`, '[\n\r]', '')

[\n\r] - This is a selection of either types of newline characters (\n for mac/unix and \r\n for windows)

GrantSmith

1

Re: Need help with Beast Mode for specific time period

@kodonnellLT If you want to create calculations for the first 6 months an last 6 months of the year, you will only need to compare whether the month number is greater or less than 6 like below.

Jan-Jun

SUM(CASE WHEN MONTH(`Period`) <= 6 then `a_amount` else 0 end)

July-Dec

SUM(CASE WHEN MONTH(`Period`) >= 7 then `a_amount` else 0 end)

Or if you need to calculate the totals for a rolling date compared to today, then you will need to use the DATE_SUB function.

SUM(CASE WHEN LAST_DAY(`Period`) >= DATE_SUB(LAST_DAY(CURRENT_DATE()),interval 6 month) then `a_amount` else 0 end)

MichelleH

1

Re: October 2023 Community Forum Highlights

Congrats to @pauljames !

Eddie Small

1

Re: October 2023 Community Forum Highlights

It was great to help so many people last month! Now, I will go back to "sleep" and let some others get the top spot for the month… as long as they aren't named @GrantSmith 😀

MarkSnodgrass

5

Re: Month Order on Bar Chart

You're using a string for your sorting so it goes character by character so it's going '1', '11', '12', '2'. You need to return numbers to do proper numerical sorting:

CASE WHEN MonthName = 'January' THEN 1
WHEN MonthName = 'February' THEN 2
WHEN MonthName = 'March' THEN 3
WHEN MonthName = 'April' THEN 4
WHEN MonthName = 'May' THEN 5
WHEN MonthName = 'June' THEN 6
WHEN MonthName = 'July' THEN 7
WHEN MonthName = 'August' THEN 8
WHEN MonthName = 'September' THEN 9
WHEN MonthName = 'October' THEN 10
WHEN MonthName = 'November' THEN 11
WHEN MonthName = 'December' THEN 12
ELSE 99999 END

Alternatively, if you have a date and not just the month name you can do something like:

MONTH(`Date`)

GrantSmith

1

Re: URL Mapping

@StuM I'd suggest using a case statement to only include the HTML formatting if the Program Guide URL like below:

case 
  when `Program Guide URL` is not null 
    then CONCAT('<a href="',`Program Guide URL`,'">',`Certification`,'</a>') 
  else `Certification`
end

MichelleH

1

Re: Historical Trend

I would suggest a re-structure of the data to something like this:

Then you could do a waterfall:

Or a running total bar, which sounds more literally what you're asking for:

Here's the MagicETL I used to restructure the data:

DavidChurchman

1

Re: Can I use variable and beast mode to change number of rows in a bar chart?

You could create a rank column using "Rank&Window" in MagicETL. Here's a dataset where I've ranked 4 products by month, then created a beastmode for "Top 3" or "Everyone Else":

Here it is filtered for Top 3:

In a dashboard setting, you could create a slicer with that BeastMode, and filter the slicer for just "Top 20", so it's more of a binary. Clicked on for just the top 20, clicked off for un-filtered.

Once you have the rank column, you could also use it for a Variable:

DavidChurchman

1