Best Of
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)
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 😀
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`)
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:
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:
Re: How to Capture Previous Month Data After on the 1st of Following Month
@elvira_bannon slight correction to my initial beast mode. The first line needs to change so that we are just looking to see if the current date is the first day of the month.
CASE when DAY(CURRENT_DATE()) = 1 THEN
CASE when MONTH(startdate) = MONTH(CURRENT_DATE()) then 'Exclude'
ELSE 'Include'
END
when MONTH(startdate) = MONTH(CURRENT_DATE()) then 'Include'
ELSE 'Exclude'
END
Re: Can there be multiple "THEN" statements?
@Laura_M1 Yes, you can add as many conditions as you want as long as each one is preceded by "WHEN" like this:
case when A then B when XYZ then 123 when blue then red … else 'Other' end
You can find more about Case statements in the Logical functions section of this Knowledge Base article:












