david_cunningham Coach

@jasonm5545 I just commented on your other post that looked like the same question. Posting here as well so you have it. The issue is the argument BETWEEN. Try this instead. CASE WHEN Donors >= Lower Budget Threshold OR Donors < Upper Budget Threshold THEN 'Normal' ELSE 'Outlier' END

@jasonm5545 The issue is the argument BETWEEN. Try this instead. CASE WHEN Donors >= Lower Budget Threshold AND Donors < Upper Budget Threshold THEN 'Normal' ELSE 'Outlier' END

For credit calculation for materialized and virtual storage rows, the number of storage rows per month is determined based on the 3rd lowest usage day of the applicable month. I've included a link to the consumption model details.

It's because you're comparing an integer YEAR(CURDATE()) to a string (fsa_spending_year). Try this instead CASE WHEN CAST(`fsa_spending_year` AS INT)=year(curdate()) THEN rtfs_amount ELSE 0 END

What particular issues are you running into? We will be able to better help you troubleshoot with a bit more info.

@Cbrack I just didn’t have anything to group by with the example data I generated to build your desired output. In your case, it could be a constant (Chem Data), or if you wanted to have multiple rows, you could group by something else (for example month or quarter).

You can use PDP for this. Set person1 to only see their data, and person2 theirs. Here are more details on how to configure.

@AngelaO415 this is not an exact match, but as far as I'm aware it's as close as you'll be able to get using the built in Domo cards. Key point: this relies on the data being structured in a particular format. This is detailed below. You'll need to figure out how to aggregate and structure your dataset to be in this…

@AngelaO415 here you go. A few things to keep in mind. This beast mode works for whole rounded numbers, so it won't format decimal places correctly. I went ahead and configured it for up to 10million. If you want to expand it you can do so following the same pattern. If you need help expanding, let me know. And here is the…

Have you already separated out the monetary value from the string? If not, my recommendation would be to figure out a consistent way to identify the location of the value you want to extract, and then use Regex to get that value. You’ll want to make sure there is no white space around the extracted value from there…

Are you saying that you need the full amount? If not and it's just formatting. Here is how you can include decimals. CONCAT('$',ROUND(`request_amount_recommended`/1000,2),'K', ' / ', ROUND(`Duration in Years`),' year(s)')

@AngelaO415 I'm guessing when you say "summing too much" you are referring to the fact that it's aggregating across the entire range, and not that the total value is incorrect. Are you saying that you only want to sum when the request_granted is True or request_state is 'granted' or 'closed'? If you are saying that you…

@AngelaO415 My recommendation would be to do something like this. CONCAT('$',`request_amount_recommended`/1000,'K', ' / ', ROUND(`Duration in Years`),' year(s)') Which gets you this output. I'm suggesting this because it's more straightforward than trying to build a beast mode that can account for variable length financial…

This is a fantastic write-up @Jonathan53891 ! Thank you so much for all your work pulling this together 😎

@nickcorona I've been playing around with this, and I'm not able to replicate your output. Is there anything that is different about the data in those rows where this is occurring? Is it just those 2 rows / how widespread is the problem? Really not sure what's going on here. My gut still tells me there is something going…

@damen I like the answer provided by @JasonAltenburg better than my own. When you get ready to select an answer, please choose his instead of mine. I was unintentionally over-complicating things by suggesting python or r. Agree with what Jason said as well about a variable length or inconsistent pattern, for either of our…

@agolla2 you can use a window function to achieve this. SUM(SUM(value)) over () Then you can use that as an input in another beast mode (you could also combine these all into 1 beast mode). Best to separate it out if you will be using it in other calculations. SUM(value)/total_hours Which can then be used to generate your…

@Cbrack if you're saying that you have some rows that are "PTD" and some that are "PTD "? If so, you would want to clean that up before doing the pivot. You can do that with the following function. SQUASH_WHITESPACE() works as follows… All sequences of whitespace characters, non-breaking spaces, control characters, and…

@Robaba04 - my recommended approach is to not try to replicate the output exactly. For example, in your original photo, row1 is 1.42 and your desired result is 2, but row2 is 13.42 and your desired result is 13. So it seems like Redshift is applying a varying rounding methodology. I'm not super familiar with the backend…

@Cbrack here is an example master_group would be whatever your row-level identifier is. You would need to create a new column for each of the values you want to pivot out.

@Cbrack - perhaps I'm misunderstanding you, but to achieve your desired output you should be able to load in the group as your column and then average your qty_percentage in the value. For example. Or are you saying that you want to turn your rows into columns in your ETL?

@Robaba04 - the answer provided by @MichelleH is great. When you select an answer, I would ask that you please choose her post. If it was me, I would take a similar approach as what she suggested, probably using UNIX_TIMESTAMP() instead of datediff and then dividing to get back to my desired date grain. Just wanted to…

So I tested using your example data, and wasn't able to replicate your output. My gut tells me that there might be something going on due to rounding or microseconds. I would suggest rounding your seconds before doing sec_to_time() and see if that fixes your issue.

Love this idea @ellibot !

@damen If you're wanting to do this as efficiently as possible, your best bet is probably using regex to input commas at your defined intervals, and then use the split string tile. This approach assumes that your desired split locations are consistent, and that you will always have the same number of splits. Alternatively,…

@Ashleigh in that case, what if you did something with DENSE_RANK()? DENSE_RANK() over (order by SUM(value) desc) You could then do something like this CONCAT('Customer ',DENSE_RANK() over (order by SUM(value) desc))

Are you wanting it to be non-repeating overall, or non-repeating where each name is the same random number?

@Lu_zhang sure, happy to help. For an example, let's say you have the following 3 sheets, all with the same column headers. Sheet1, Sheet2, Sheet3 You can use the following formula to combine all 3 =UNIQUE(QUERY({Sheet1!A1:C;Sheet3!A1:C},"WHERE Col1 IS NOT NULL ORDER BY Col1 ASC")) You would adjust with your sheet names…

Does your dataset always have data for each day? In other words, if on a particular day, the value is zero, will it show up as a row with the date, and a value of zero, or is the row for that date simply not in the dataset? If the former, here are some details on how you could achieve this. Note, this also assumes that the…

@Caua_Soares just wanted to make sure you knew that the below is always an option 😃 While this isn't a perfect solution. One way you can achieve this now with any currency symbol, is to format as a Number, and then paste in the currency symbol you are wanting to use. You will still have to implement a methodology for…