Best Of
Re: How to get an avg in the grand total of a column with a BM with sum?
@Pam1324 Since the only difference between the calculations for Month 10 calculation and all other months is the numerator, the grand total will work more cleanly with the case statement inside the first sum like below. Aggregations work best when they are outside of the case statement, rather than inside them.
sum(case when month(`cycle`) = '10' then `$ new Annual Salary(in USD)` else `$ New Annual Salary(custom)(in USD)` end) / sum(`$ Annual Salary(in USD)`) - 1
Re: Month Over Month % Change of Total Number
@MichelleH There are, that's what I was trying to use with the IFNULLS and NULLIFS. With your perspective I was able to get the correct calculations, pictured below.
I also realize I could have done this NULL reassigning in an ETL for simplicity's sake, but I'm just happy it works.
Re: Subtotals in PivotTables
The thing there is that you have 2 rows, Level and Spend, and I'd guess each level is tied to only a single Spend based from your screenshot, if that is not always the case then you want to keep it the way it is, as the numbers will not always be "duplicated". As a workaround, you could display only one or a single one that is the concatenation of both values, not the prettiest solution but something that you can apply right away. I agree with @MichelleH and having a feature that would allow us to indicate the number of levels at which we want subtotals calculated would be awesome.
Re: Subtotals in PivotTables
@m_malecka I've run into that issue as well and have not found a way around it. I'd suggest contacting Domo Support about the "Hide Subtotals" option not working and posting to the Ideas Exchange to about enhancements to the subtotal functionality.
Re: Count Distinct with Fixed?
@Domomon DISTINCT doesn't work with window functions, and I believe that's no exclusive to Domo, as I think that goes for MySQL and SQL Server as well. I've seen that one solution is to use DENSE_RANK() instead of COUNT(DISTINCT ), but that does take nulls into account, so just keep that in mind. If that doesn't work, you'll need to use an ETL and use a GROUP BY and joins.
Re: Using Variables in Beast Mode
Here's a re-worked case statement:
CASE
WHENDate of Deathis NULL THEN FLOOR(DATEDIFF(CURRENT_DATE(),Date of Birth)/365)
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 1 THEN '<1'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 3 THEN '1-3'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 6 THEN '3-6'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 9 THEN '7-9'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 12 THEN '10-12'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 15 THEN '13-15'
WHEN FLOOR(DATEDIFF(Date of Death,Date of Birth)/365) < 18 THEN '16-18'
ELSE '18+'
END
Due to how case statement functions, you are able to remove the between logic and just use less than logic which makes the statement a bit more concise.
Hope this helps.
Re: Beast Mode
You can double click on the beast mode in the fields pane in the beast mode editor and it will populate in your beastmode - that way you don't have to re-write the logic again:
