Best Of
Re: Trying to calculate Rolling Averages/Sums over the previous year
You'd have to do this in the dataflow -basically join the 12 prior monts to each month (Aka, April's date would have the prior 12 months appended to it and all have a "Report Date" of April, so in analyzer when you select April you'd have the 12 months there). If you don't want the ability to drill, you could aggregate this before appending it.
Re: Disable Auto-Preview for Cards (Federated Connections / System Wide)
It almost always picks the dumbest fields possible.
Like - Let's say it's transaction data…It will give you something like:
Bar chart with the vendor created date (or most irrelevant date possible), value as the sum of item number, and series of transaction id. It boggles the mind, and I wonder what the backend logic is. If it were taking the first date, numeric, and text fields of the dataset, we could at least plan data structure to give it a good start. Alas, it does not.
For the card date, it does take the first date field, so that's something, though I still see so many mishaps from people not checking that! @RobynLinden's idea for showing that more prominently could help a ton and reduce frustration.
Re: Issue with Sales Data Tracking and Date Transformation
You'll need to stack / pivot your data so you have a singular date field instead of multiple per row.
You data would look something like:
Order ID | Date | Date Type | Order Total
1 | 1/1/2023 | Created | $123
1 | 1/7/2023 | Sales | $ 123
Then you can use the new Date field to make sure you're getting records affected by each. If this is single line sales data it can get duplicated if it happens in the same year so you'll need to dedupe your data or consider it as part of your calculations. You can use a window function to divide your order amounts so that when the multiple lines get added together it'll add up correctly.
Something like this might work.
SUM(`Order Total` / COUNT(`Order ID`) OVER PARTITION BY `Order ID`)
Re: Data Snapshot
Thank you both for the suggestions. I will take a look at both and let you know what I found.
damen
Re: Is it possible to set 12 complete months rolling date range?
For reference here's a beast mode I like to use with LAST_DAY to determine if the date value is in the current month.
CASE WHEN LAST_DAY(CURRENT_DATE) = LAST_DAY(`date`) THEN 'Current Month' ELSE 'Not Current Month' END
LAST_DAY returns the last day in the month for a given date. CURRENT_DATE returns the current date
Re: Partition By not working to show correct SM% when I change parameters view options
Your case statements don't have any clause for when Geography/Product View = 'P&L'
Either add an else 1 at then end of each case statement, or call out a specific value for each option that the user can select.
Re: Beast Mode Editor Formula List Consistency
Agreed! It would also be helpful to update the function list in this KB article:
Re: Does Beast Mode support using "Contains" to Identify Value Count
@afieweger Yes, you will use LIKE and wildcard characters in your case when statement:
SUM(case when PresentationName LIKE '%self-service%' then 1 else 0 end)
The % symbol will just say there can be any number of characters before or after 'self-service'. I like doing counts with an equivalent sum expression.
Re: Does Beast Mode support using "Contains" to Identify Value Count
you can use the LIKE operator with the % sign on each side to make it look for the phrase contains. Would be something like this:
CASE WHEN fieldname LIKE '%self-service%' THEN 'Self-Service'
ELSE 'Something else'
END
Re: Pivot Table - More control over subtotal rows
The suppress single line subtotals work up to a certain point and with certain limitations from what I've tested in the past:
- Sometimes you want some subtotals to show even when there is a single line, to drive consistency in presentation. In Mark example the second block (104471) is the perfect representation of this, if you select the remove single line subtotals then that will be presented differently than all of the other entries. Which makes it less intuitive and harder to read for the untrained eye.
- For this to work you need to make sure you put the line that makes it unique in the right position from left to right as of when you want to stop the groupings from happening (usually a unique identifier to the highest level of granularity to be displayed). Which might not always align with what the desired order of the output is.
Having an option to select how many levels of Subtotals we want would be ideal and grant the level of customization many users are looking for.
Visually if our Dataset would be something like this:
And we want our Pivot table to show the columns in the following Order: Customer > Month > Invoice > Total, but we want our Subtotal Rows only to be present at the Customer level and to always be present at that level, without the Suppress single line totals we get:
If we select the option, then we still get subtotals at the month level when there are multiple invoices and we lose the Total row for Customer C.
If we rearrange to have the Invoice as the second column (highest granularity) we do get all subtotals to only be for the first column, but still miss to have one for column C (this is way more noticeable if you style your subtotal rows, which I've done in this last screenshot to better illustrate how those can easily be missed)
As you can see it'll be hard for the untrained eye to know that the C row has the same level of importance as the A Total since it's not even highlighted in the same way.
