Best Of
Re: concat() integers
So this answer makes use of a Beast Mode provided by @JacobFolsom from 2018. Thanks Jacob!
This answer is set up for a max of 9 digits. You can modify it if you need to handle more than that.
An example below. Value shows the base value, formatted with commas as Domo automatically does. The next column shows the CONCAT() label, where we can see the formatting is removed. The last column shows the CONCAT() label, but using a formatting beast mode.
Value Formatted is achieved using the below beast mode from Jacob.
CONCAT('Label: ',CASE
WHEN LENGTH(ROUND(SUM(Column))) = 9 -- 9 digits = hundreds of millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 3,3), -- second comma block
',',
SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block
)
WHEN LENGTH(ROUND(SUM(Column))) = 8 -- 8 digits = tens of millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block
',',
SUBSTRING(ROUND(SUM(Column)),6,3) -- third comma block
)
WHEN LENGTH(ROUND(SUM(Column))) = 7 -- 7 digits = millions
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 2,3), -- second comma block
',',
SUBSTRING(ROUND(SUM(Column)),5,3) -- third comma block
)
WHEN LENGTH(ROUND(SUM(Column))) = 6 -- 6 digits = hundreds of thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,3), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 4,3) -- second comma block
)
WHEN LENGTH(ROUND(SUM(Column))) = 5 -- 5 digits = tens of thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,2), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 3,3) -- second comma block
)
WHEN LENGTH(ROUND(SUM(Column))) = 4 -- 4 digits = thousands
THEN
CONCAT(
SUBSTRING(ROUND(SUM(Column)),1,1), -- first comma block
',',
SUBSTRING(ROUND(SUM(Column)), 2,3) -- second comma block
)
WHEN LENGTH(ROUND(SUM(Column))) < 4 -- < 4 digits = hundreds or less
THEN ROUND(SUM(Column))
END)
Another option would be to divide your output value, and then format those abbreviated values. I.e. - instead of 100,000 | 100k, or 1,000,000 = 1,000K or 1M. That would require a different beast mode though.
Re: Can I turn over provisioning of new Domo users to my internal IT team to handle it all using Okta?
Hi @TiagoV - apologies to you and to the community for my lack of follow-up on this one! It took a while to get a final answer and I lost sight of my comment here.
So it turned out the org had previously configured an integration with Okta that was passing user attributes that we were leveraging to define Dynamic Groups in Domo. The reason we weren't seeing users get automatically created was a checkbox. Under the SAML (SSO) settings under Authentication in the Admin panel, we had to uncheck the box in "Advanced settings" for "Only invited people can access Domo."
For deprovisioning, we went with using Tray to receive an Okta deprovisioning webhook signal to then send an API call to Domo to change the user to a "Social" role, which basically shuts off user access but preserves anything tied to their user in Domo. (Users can be removed later if needed manually in Domo once all migrations are complete.)
Hope that helps!
Re: Dropdown Selector: always have pre-selected the latest date on a filter card.
If you create a beast mode using a window function, you can get the latest available data.
CASE WHEN ScheduleDate = MAX(ScheduleDate) over () then 'Latest Data' else ScheduleDate end
This will make the latest data show as "Latest Data" and all other data show as the date.
You can then set your default to "Latest Data"
Re: April 2024 Community Forum Highlights
Thanks @anna.yardley! It's been wonderful to collaborate and learn with everyone. We really have a great and welcoming community 😁
@Grant.Stowell & @ArborRose appreciate the kind words!