Best Of
Re: How to indicate story pages and regular pages on page list
Hi @eriena, the Governance Dataset connector currently doesn't have the field 'Page Type' or similar, so it's worth making a request for that in 'Ideas Exchange' or asking your Customer Success representative to investigate in anticipation for the next version update (It looks like it was last updated in April). As a workaround, you can create a beast mode. An example is:
CASE
WHEN 'Page ID' = '1234567' THEN 'Story'
WHEN 'Page ID' = '2345678' THEN 'Regular'
ELSE 'Not Mapped Yet'
END
You would continue to manually add in different instance of your pages. Hope that helps.
amehdad
Re: How to aggregate CASE Statement values?
don't use SUM(Distinct) that's too risky.
Imagine if the forecast value for a set of employees is all 50. SUM distinct would assume there was only one employee with a forecast value of 50.
The better solution is to reshape your data such that you don't have to rely on DISTINCT to deduplicate rows. have a set of rows for the employee forecast, have a second set of rows for the regional forecast and a third set of rows for the state forecast. or however you're rolling up your forecasts.
Re: Formatting Numeric Values within CONCAT functions to Currency
I recommend formatting it to currency with the concat function in a beast mode. If you try and do it in an ETL, then they would become strings and the card wouldn't be able to sum them when a user is interacting with the card. Here is a beast mode that will format it for you and abbreviate the millions and thousands:
CONCAT('$',
(CASE WHEN INSTR(SUM(`totalreported`),'.') = 10 THEN /*xxx million*/
CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'M')
WHEN INSTR(SUM(`totalreported`),'.') = 9 THEN /*xx million*/
CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'M')
WHEN INSTR(SUM(`totalreported`),'.') = 8 THEN /*x million*/
CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'M')
WHEN INSTR(SUM(`totalreported`),'.') = 7 THEN /*xxx thousand*/
CONCAT(LEFT(SUM(`totalreported`),3),'.',SUBSTRING(SUM(`totalreported`),4,2),'K')
WHEN INSTR(SUM(`totalreported`),'.') = 6 THEN /*xx thousand*/
CONCAT(LEFT(SUM(`totalreported`),2),'.',SUBSTRING(SUM(`totalreported`),3,2),'K')
WHEN INSTR(SUM(`totalreported`),'.') = 5 THEN /*x thousand*/
CONCAT(LEFT(SUM(`totalreported`),1),'.',SUBSTRING(SUM(`totalreported`),2,2),'K')
END))
Here are two other examples of how to do it.
https://dojo.domo.com/discussion/52677/domo-ideas-conference-beast-modes-number-formatting#latest
Re: % of total in dynamic Textbox
Percent of total typically requires using window functions in beast modes, which is not enabled by default. If it isn't enabled, ask your CSM to enable window functions in beast modes. You can then use the following beast mode to calculate the percent of total:
sum(amount) / sum(sum(amount)) over ()
Re: Ignore filters + include ALL entires of a specific measure in a formula, but filter other measures
@pl_Luke it's unclear to me how you're defining the numerator and denominator. it sounds like you want to be able to choose the reasons included in the numerator and then always have it divide by an 'all member' i.e. all activity.
if that's the case, then just UNION the data onto itself. but add a column [Report_Reason] which contains the Reason column for UNION_A and then a constant Denominator for UNION_B.
Then instruct your users to always include "Denominator" and they can choose the members of the numerator.
Re: How to Trim a column with multiple words in it using ETL
@Ashleigh is correct.
Here's an ETL2.0 example
TRIM(`Column`, 'CHARACTER TO STRIP')
If you leave off the second parameter it'll strip spaces by default.
Magic 1.0 example:
Re: How to Trim a column with multiple words in it using ETL
If you have Magic 1.0, you should be able to use the string function tile to trim spaces.
Ashleigh
Re: How to Trim a column with multiple words in it using ETL
@learnx do you have magic ETL 2.0? If so, you can use a formula tile. I believe there is a trim whitespace function.
Ashleigh
Re: How to use programmatic filter with domo
Hi @sunny
How is it not working? What have you tried? Are you able to log into the sample login page with mike as the user? Did you start up the yarn server to connect to? There's a lot of moving pieces with programmatic filters. It'd be helpful to know what you tried and what the outcome of it was.
Re: Beastmode | Exclude data based on dates
Clean up your SQL and avoid using nested CASE statements. you want your code to read cleanly.
case when `Date` > '2021-07-01' AND `company` like '%company1%' AND `ConversionTypeName` like '%product1%' then 'exclude' else 'include' end
FYI it is syntactically correct to nest your logic using parenthesis, but again... avoid nested CASE.
case
when
`Date` > '2021-07-01'
AND (`company` like '%company1%'
OR `ConversionTypeName` like '%product1%' then 'exclude' )
else 'include' end