PJG Member

Ah, as simple as that! I was looking in Admin settings. Thanks, Grant!

Hi Arbor, Thanks for the reply. What you described is more or less exactly what I'm doing in another ETL. However, don't think I can do that here - group by ProjectId - because I need to keep each of the monthly rows in order to calculate project costs in the same output dataset. Expanding on the mockup from my first post,…

I'm just struggling with how to integrate that with my other criteria within the ETL BM. Thanks for your patience.

Hi Grant & Arbor, thank you for your replies. Trying to figure this out based on your answers, but quickly for now, I should have clarified that there are other BatchTimeStamp values in this dataset, and I do need to filter on this one specifically. Also, far from an expert on BM so any hand holding is greatly appreciated!…

Hi all, Follow up to this. I took a before and after of my card to ensure it was accurately converting the positive numbers to negative numbers, and in most cases, it worked as intended. However, for certain Beast Modes, it is invoking the 0 instead of converting the number to a negative. Here is one example: MAX(CASE WHEN…

Ah, as simple as that, and I was looking for a function to do it! :) Thanks!

Hi Randall, Just finally having a moment to come back to this today. Thank you for your help on yet another topic! I think I have it working, but it's potentially highlighting some issues with my Beast Mode that I will look into separately & validate. Just to confirm this is how I set up each of the Appends? Configuration…

Thanks @ArborRose - that did it; well explained, thank you!

Thanks again, @rco! Changing -1 to 0, and removing the Correct for Dummy tile did it!

Perfect thank you!

Hi Randall, One more on this, as I think it's related to the format of the output dataset I was trying to add Project Count as the Summary. I did a simple Beast Mode: Biologics+CGT+CHI+China+Communication+Finance+HR+Group Operations+IT+Legal, Risk, and Responsibility+Quality+Small Molecules However, this was showing a 0. I…

Did exactly what I was thinking with the ETL, and it works perfectly:

Thank you! I did figure out the formula piece already, and have used this in a couple of cards! In one card, I need to group some of the phases, so wrote a BM for this. Small exert: when Phase_p = 'A2 - Assessment Backlog' then 'Planned' when Phase_p = 'A3 - Ready for IT PB' then 'Planned' when Phase_p = 'E0 - Pending…

Picture heavy, but step by step, here's what I have: (Same config for Org) Like I said, my Add Constants and Append…. and maybe formula are where I'm not sure I've configured them correctly. Thanks!

Hi Randall, Thank you for taking the time to put together this incredibly detailed explanation. I think I've followed successfully up to the ProjectId Const, and I think I understand the configuration for all but two of the tiles. Could I trouble you to share the configuration for the ProjectId Const and for the Append…

Hi yes, per ProjectId is what I'm looking for, and again, sorry if I explained that poorly! Each ProjectId could appear in the dataset dozens of times, but each ProjectId will have the same Yearly Total RUN Costs for Live and the same Yearly Total RUN Costs for Nov 2023, so I only needed to identify a value for each…

Maybe I was making this too complicated, or explained it poorly, but I tried just adding this to the existing Group by before getting to your reply, and this looks to be working.

How can I check my DOMO version? There's a few things I don't have access, as it's a vendor controlled layer over another system that we use, and I know some features are restricted (Buzz for instance). Yes, for "Yearly Total RUN Costs (CHF)_p", I just need to show the only value per project for Live and only value per…

Hi Randall, please let me trouble you for one more question on this. I have another column, Yearly Total RUN Costs (CHF)_p. It's the same for every row, so I don't want to SUM it, but I do want it displayed in the output dataset, and I again need to distinguish between Live and Historic/ 2023-11-22. Because I'm not using a…

Beautiful, thank you so much again!

Randall, thank you so much for all your help on this. Understood, and perfectly explained. I realized I needed to wrap my other code like that too, and it is now working perfectly! Any way to force blanks to appear as 0 in the output dataset?

I did have SUM() initially, but I read elsewhere that it wasn't needed, and Group would SUM it without that, so took it out. However, tried adding it back in, and same output unfortunately. To troubleshoot, I tried temporarily changing the Group to only include the Live code: I then did Run Preview and checked the dataset…

Hi @rco, thank you! Progress! The Nov 2023 Planned Cost column is now populated for all projects with a Historic timestamp of 2023-11-22, which it wasn't before! Thank you! However, it is not populating the Live Planned Cost column if the Nov 2023 Planned Cost column is already populated. I.e. only one of the two columns…

Ah, understood. Yes, my output screenshot is just a simplified version of what I need. Adding in my extra columns is working as intended. The issue is just that my output is only giving me the total for some of the Live column, and for none of the Nov 2023 column. Not sure if it's an issue with my Beast Mode, or if it's…

I believe I need the timestamp as there are other RecordType = Historic with different BatchTimestamps. In this case, I only care about the Nov 22, 2023 12:00:00 AM one. Not following what you mean by bottom branch not doing anything… it's needed to add back in some extra columns that I need that were removed with the…

Oops, sorry, I thought I had that in there. Added to my original post.

Hi nmizzell, you were too quick :) I just fixed that piece and edited my post above. I have it running, but there are only 0's in my new total columns (actually, for Live Planned Cost, there are 0's and 1's). Is my Beast Mode wrong? Thanks!

Hi both, Thank you for your replies. I was starting to lean toward the issue being the way the dataset was configured, so I have created a new dataset with an ETL, where I'm using a Group By to SUM the Planned Costs. This ensures there is just one row per project, and after using Arbor's beautifully cleaned up Beast Mode,…

Thank you for pointing me in the right direction, Colemen. Got it working with this:

Sorry, ignore me, I found the culprit; Under Chart Properties > General > Fixed End Date - this was set. I forgot this existed.