BlueRooster Domo Employee

Luckily I had to do the same thing a couple weeks ago. Here's the query I used in a SQL transform to generate a 'Percentile' column on the dataset. You should be able to edit this to fit your needs: SELECT a.*, ROUND(100.0 * (SELECT COUNT(*) FROM inputDatset AS b WHERE b.`Values` <= a.`Values` ) / total.cnt, 1 ) AS…

@user04681 Adding a link to your Idea post for visibility. https://dojo.domo.com/t5/Ideas-Exchange/Conditional-formatting-relative-to-other-metrics/idi-p/30703/jump-to/first-unread-message Thanks for the submission ValiantSpur

One thing you could do would be to create a BeastMode to check the current value against the goal. Something like this: CASE WHEN `Current` < `Goal` THEN 1 WHEN `Current` = `Goal` THEN 2 WHEN `Current` > `Goal` THEN 3 END Then you could use the new BeastMode field in your color options like so: From there you could repeat…

So we have a similar situation that sometimes arises where people may not have metrics for a specific time period but we want to show them regardless. What we end up doing is pulling the list of 'Active' users from our HR system of record and then left join that to whatever data source we want to summarize. We end up…

No worries! I've done it many times myself. Always good to have a second pair of eyes ?

Looks like you might be missing a D from ADDDATE and the END of the CASE. Try this: CASE WHEN ADDDATE(CURRENT_TIMESTAMP(),-1) < `DeviceLocalTime` THEN 1 ELSE 0 END If that's not it, let me know and I'll take another look. Sincerely, ValiantSpur **Please mark "Accept as Solution" if this post solves your problem **Say…

Ok, so to do that you would need to create a data transform. You could do something like this: Transform 1: SELECT *, MAX(`date`) as MaxDate FROM dataset Transform 2: SELECT * from transform 1 WHERE MONTH(`date`) = MONTH(`MaxDate`) AND YEAR(`date`) = YEAR(`MaxDate`) This would limit your data so you could display date…

What's the actual date range you would like to display? If you uploaded on Feb 14, would you want to see Jan 14-Feb 14 or Jan 1 - Feb 14? You could change the timeframe to something like Last 30 days. The Last Month option would look at it as Jan 1st to Jan 31st. Once I know a bit more about what behavior you want it to…

Paolo, A couple of things to check. With your percentage calculation, are you doing the 'Multiply by 100' option in the column format as shown below or multiplying by 100 within the beastmode? If it's in the column options as shown below, you may need to change your rules to <=.8 and >= .81 Second question. Which lollipop…

Currently the only way to have an aggregate value like that for all rows would be the dataflow as you described. But you would lose the date filter option you're looking for. There is however an idea "Under Review" for something like what you're talking about. We would need almost a different functionality type for…

@John-Peddle feel free to go ahead and open a new topic if you have questions around something else. That will help make your new request searchable for someone else if they have the same question later, as well as increasing visibilty for others to respond.

Do the values that fail to map all have characters like that comma in them? If so you might be able to do something like the below. Possibly use a Replace Text option using the RegEx check box and replace the comma with empty string. Then it may process your value mapping correctly. If that doesn't work, perhaps you could…

Thanks @ST_-Superman-_ I'll keep that in mind for our date groupings, that's a good deal cleaner than what I was using. I'll still probably have to use some long Case When's for some other cards that group by things like line items on a P&L, but at least it's workable. 

Ok, after hearing it put that way it makes more sense. Without the sort on the first column it would indeed get very jumbled. Thanks for the clarification.

Nick, I would switch to the File Upload connector type. This was the upgrade for csv, xls, xlsx connector types. The File Upload connector type will allow you to "Run Now" from the wrench in the top right of the connector properties and have your Excel files get updated.…

I don't know about elegant. But in one area of our business where the data was updated less frequently, I needed a way to always show the most recent months worth of data. I ended up creating a SQL transform where I appended a new column that searched my date field and found the MAX date there (repeating that field for…

@JeriLarsen @mwillder @SamHoward We were actually discussing this tool earlier today. This is going to help us meet a large need that we had as a small BI team serving a large user base. Previously we have been creating tutorial videos to help train our users. The problem with doing that for all of our needs was the time…

Here you go: CASE WHEN `Followers` >= 0 AND `Followers` <= 9999 THEN 'Bronze' WHEN `Followers` >= 10000 AND `Followers` <= 99999 THEN 'Silver' WHEN `Followers` >= 100000 AND `Followers` <= 499999 THEN 'Gold' WHEN `Followers` >= 500000 AND `Followers` <= 1000000000 THEN 'Platinum' END

What's the datatype for your `Followers` field? In the analyzer when you're doing the beastmode, what does the icon next to the field name look like? Is it a '123' like below?

Glad that was a help to you. If you get hung up on anything else, just let us know. -ValiantSpur **Please mark "Accept as Solution" if this post solves your problem **Say "Thanks" by clicking the "heart" in the post that helped you.

You sure can. Just modify this to your own use: CASE WHEN `followers` BETWEEN 1 AND 50000 THEN 'A' WHEN `followers` BETWEEN 50001 AND 100000 THEN 'B ..... END Hopefully that's what youre looking for. If not, let me know and i'll try to help further. Sincerely, ValiantSpur **Please mark "Accept as Solution" if this post…

One question on your data. For the 'Solution Play' dimension, is this a variable for each account or does each account have the same number of solution plays?

Matt, Not sure on the PPT part but as far as HTML on the mobile app, we had actually submitted a support ticket back in October and were told the development team was aware of the issue and would let us know once a fix was in place. Haven't heard anything since, so I'm assuming it's still an ongoing issue. If it has been…

I haven't heard if this is the case. (And I hope not). We use both publication groups and PDP at our organization. They both have their own use cases, so I don't see why they would get rid of it. Have you heard something I haven't? 

Ok, give this a shot. If it works then use this as your output dataset and then replace the EpicProduction dataset with this in your ETL. SELECT *, CONVERT(LEFT(RTRIM(LTRIM(`AcctZipCode`)),5), UNSIGNED INTEGER) as 'CleanZip' FROM epicproduction This should let you join directly to the ZIP field (hopefully).

Ok, at this point I would switch over to a SQL transform and we'll have to get our hands dirty. I would use the following to identify what non numeric zips are currently in my data: SELECT `AcctZipCode` FROM EpicProduction WHERE `AcctZipCode` NOT IN (SELECT `AcctZipCode` FROM EpicProduction WHERE `AcctZipCode` REGEXP…

It sounds like you have some non-numeric fields in your data that isn't allowing for conversion to whole number. It's going to be a bit more tricky in ETL than a SQL transform but you should be able to do the following. If say you were able to identify that you had "-" in your zip codes for something like 96875-2486 then…

@John-Peddle That's correct

In your example a count on the beastmode would return 5. Once a value is found in a multiple OR statement like that, it immediately dumps out to the THEN and ENDS. It wouldn't get caught multiple times in the same count. Hope that clarifies. If not, please let me know. Sincerely, ValiantSpur **Please mark "Accept as…

Sounds good. If it still gives you problems let me know and I'll see what else we can do.