About jbrass

jbrass · ‎06-29-2022

Makes sense. Thank you. As often is the case, this becomes a trade-off between model size and complexity. The method you mentioned would increase complexity, especially given the way that our formulas are already a bit long given the structure of the module. It is a viable method in the event that the output module just becomes too large once opening it up with the users dimension as well. Appreciate the response; definitely got me thinking. Thanks, Josh

jbrass · ‎06-29-2022

Hello - I have a question which ultimately is related to using the users list in module design. Was hoping someone could confirm my theory, or even better, provide a more efficient way to set this up. Here are the details on what I'm dealing with: We have a module setup for budget vs. actual analysis, in which the module is dimensionalized using a list which has 3 items: 1) version 1 2) version 2 3) variance Our model does not use native versions, but rather we have a "versions list" list that we use to house the versions. So, the logic is such that the user can select which of the items in "versions list" is represented in #1 and #2 above, and #3 will show the resulting difference between those 2 versions. This generally works, except that we're seeing that users are often overriding each others selections of which version is represented in #1 and #2. This can cause some frustration. So, we were considering using user-specific version selectors. Is there any way to do this other than dimensionalizing the entire module to also include the users list? I understand that the selection itself of which version is shown in #1 must be dimensionalized by user, however I just want to make sure that we need to add significant size to our calculation module to accommodate this. Please let me know your thoughts. Thank you, Josh

jbrass · ‎12-08-2021

True that you are restricted to one set of actuals using switchover. Makes sense. Thanks Andrew

jbrass · ‎12-07-2021

Thanks Rob. Very helpful. The initial consideration was to still use versions but just not use switchover, however you bring up a good point: maybe we shouldn't use versions at all. I think it comes down to how much we value the ability to use version subsets. Appreciate the insight. -Josh

jbrass · ‎12-07-2021

Hi All - In the context of an FP&A use case, we're trying to evaluate whether to use switchover or just build a system module to dictate where the forecast begins and the actuals end. Would love to hear about pros and cons of both approaches. Here's what I have so far: Pros of using native switchover: 1) Ability to use formula scope, thereby allowing for a formula to work for actuals and then allow for direct data input for forecasted months. Cons of using native switchover: 1) Potential for reducing size of model by removing "Actual" version altogether. Any more insight/preferences/best practices would be appreciated here. Thank you! Josh

jbrass · ‎08-31-2021

I figured it out. I setup a rankcumulate on the "Max to Allocate" field using the priority field. Then, setup a boolean called "Include Whole Item?", which looks if the "Amount to allocate overall" > the rankcumulate. Then another field called "Whole Item Amount", which is "IF 'Include Whole Item?' then Max to allocate else 0". Then I setup another line item dimensionalized the same way as the "Amount to Allocate Overall" field, called "Remainder After Whole Item Allocation". This is the "Amount to Allocate Overall" minus the Whole Item Amount. Then I did a rank function back at the priority field, excluding the items that were TRUE in the "Include Whole Item?" line item. I setup another boolean called "Include Partial Item?", and if the Rank function = 1, then this is TRUE ELSE FALSE. This identifies the item that has an amount needed but that is less than the "Max to Allocate" amount. Another line item called "Partial Item Amount" = IF Include Partial Item? THEN Remainder After Whole Item Allocation[LOOKUP: Contract] ELSE 0. Final result is that the "Allocation" line item shown in my image = Partial Line Item + Whole Item amount. Hopefully someone gets use out of this at some point 🙂

jbrass · ‎08-31-2021

Its likely not really a large list but I just wanted to make sure it was built in a way that could accommodate it if necessary. I'm talking about the list of Items (Item 1, 2, 3, etc). In actuality it likely would be a very small list (no larger than 50).

jbrass · ‎08-31-2021

Hi All - Was hoping I could get some ideas/help on this problem that I'm trying to solve. I am trying to automate the output of the "allocation" line item shown below. I have a set amount that I need to distribute into various list items based on the priority shown. The max amount allocated is shown in the first column. I'm looking for a solution that can scale; assume that the list of items is large. Not looking for logic that says something like "if priority = 1 then X else if priority = 2 then Y". Hope this makes sense. Thanks, Josh

jbrass · ‎07-19-2021

Got it, thanks again for looking into this.

jbrass · ‎07-19-2021

Thank you Rob. I think this helps in ensuring that the number shown in shown in the correct multiple (i.e., if millions, show in millions, etc...), however I'm still struggling to show that "%" sign if necessary. Please let me know if you come across any reasonable solutions for this. Thanks, Josh

jbrass · ‎07-13-2021

Hi All - Question related to this: What if my data has to be displayed in different formats? For example, I have a P&L view that has some %s mixed within (such as '% of revenue'). Any suggestions on how to show a variance analysis view given that the collect function can only have 1 format? The other way I've been testing is to setup a list that includes 1) actual 2) forecast 3) variance as non-production items and then effectively pointing to those list items within the formula, however this also is restrictive if I want to add a 4th list item for "Variance %", for example. I think I just wish that the collect function could inherit the formatting of the source module?! Any help much appreciated! Thanks, Josh

jbrass · ‎05-20-2021

Hi All - Thank you for your help here. It figured out the root of the problem. There was a line item that had version scoping set to "actual only" and the formula that I had there pointed to a forecast version. This logic was working in the model but seemed not to like it when the switchover changed. I removed that logic (although I did want it!) and switchover changes now work. Thanks, Josh

jbrass · ‎05-11-2021

Hello - I'm trying to move forward the switchover date for 1 version in a model and I'm getting a circular reference error message. This is the first time that this has happened. Any ideas what would be causing this / how to fix? FYI - the error message shows a giant list of references that it says are impacted. Thanks, Josh

jbrass · ‎03-24-2021

is there anyway to reference the next or previous item in a list by using a formula? I saw a post a while ago that inferred that this was not possible without quite a bit of conversion logic. thanks, josh

jbrass · ‎03-23-2021

If you don't have "all Periods" in the time dimension, then you can use a TIMESUM formula with a blank "start period", which effectively sums all periods. The formula would be: TIMESUM(Module with time.Amount) = 0 The only problem with this is that it effectively takes the sum of all periods, so if you have a -$1000 in one period and then a +$1000 in another period, then it treats it like a blank. The overcome this, you could setup a 2nd line item in the module that includes time to dictate whether or not each cell has a value. Maybe call this line item "check if blank". So the formula would be: IF Amount <> 0 then 1 else 0. Then, point that TIMESUM formula to the "check if blank" line item instead (so it would be TIMESUM(Module with time.'check if blank')). Hope this helps! Josh

jbrass · ‎03-22-2021

Thank you @kevin.cho , this works perfectly. Thank you @rob_marshall and @Misbah for your help and commentary as well.

jbrass · ‎03-20-2021

Thanks Rob. That’s the approach I’ll take. Was trying to see if I could skip the import process (that we’d use to populate that email list), but it sounds like that’s not possible. Appreciate your help on this! Josh

jbrass · ‎03-19-2021

Thanks Rob. That makes sense. I was trying to see if there was a way to do it without having to run an import process (to create that list), but it sounds like this is the only way. Thanks again, Josh

jbrass · ‎03-18-2021

I can actually use email (which is a text field) instead of name to make it a tighter match. Understand that this may have holes, but this is just being used to flag potential issues so I'm not too concerned. Thanks, Josh

jbrass · ‎03-18-2021

Hello! I'm running into a problem that I'm struggling to solve and was hoping that I could get some help here. I have a numbered list that represents employees. Sometimes, employees are converted from part-time to full-time and when this happens, they are assigned a new employee ID. I need to be able to identify cases where there are multiple list items that represent the same individual (based on a text field that shows their name). In excel, this would be accomplished by using a countif formula. I'd like to know if there is a simple solution that I'm just not seeing. I think I could create a new list based on the concatenation of employee ID and name, and then have the name be a list property and then do a count based on that, but I'd like to know if there is a solution that requires less steps (preferably no need for a new list or import action). Screenshot below; The "Count of items" field is what I'd like to make formulaic. Thank you! Josh

jbrass · ‎03-11-2021

Hi @DaanishSoomar Thank you for your quick reply. The goal is to show a rounded number throughout. If possible, I am trying to solve this without rounding the aggregated number (using formula as summary method). That $.01 (in this example) is problematic, as I'd like for this to be exported to excel and then for a user to be able to sum that data again and get the same end result. So, to answer #3 below, I do not expect for the rounded and unrounded numbers to be the same (in this case I want the rounded number to be 41.63 while the unrounded number is 41.62). My goal would be not to build a ton of additional logic to accommodate this. Given this context, can you provide more insight into your proposed solutions? For context, let's assume that the row set in question is a million rows (not the 4 row example that I screenshot). Thanks, Josh Thanks, Josh

jbrass · ‎03-11-2021

Hello, I'm having an issue in which a sum of rounded items is still showing some unexpected decimals. See screenshot below. Is there a workaround for this other than using formula as the summary method? Thanks, Josh

jbrass · ‎12-02-2020

Completely agree with this. I'm trying to find out what list format is being used for a specific line item and there is no way for me to know, as the list name is longer than what is visible in blueprint mode, and I'm in a deployed model so I can't double click into the cell to see it that way. Copying and pasting to excel doesn't show me the name of the list either.

jbrass · ‎06-25-2020

Agreed. I do not currently know of a way to restrict the user from un-hiding potentially sensitive information by simply showing columns. The only way that I know to protect such data from the end user is to create a separate module that does not include that data, and do a bunch of linking between modules which is a waste of time and workspace. Unless anyone has any ideas of how to accomplish this using existing functionality? Thanks, Josh

jbrass · ‎06-11-2020

Hello, I think it would be helpful to be able to set default options for the "Show New Content" selections in the Contents section of the settings panel. Currently it defaults to "On" for new functional areas. This can be problematic, and could accidentally provide roles with access to modules and dashboards that they should not have access to. If anything, I'd rather this default to "Off", so that the admin is required to provide access. I'm sure there are plenty of other items on the roadmap but figured I'd point this out in case it's a relatively quick fix. Thanks! Josh

jbrass · ‎01-17-2020

My extract has multiple lines per SFDC Opp ID, so I'd need to get a unique ID for that Opp ID/Product ID combination, and I'm working the SFDC guy in house to see if we can get that. Was working on this as a backup method in case we can't get that. Anyway, this works! Thanks for your prompt help!

jbrass · ‎01-17-2020

Update - figured out the rank function. Thanks so much! Rob - still open to discuss the approach.

jbrass · ‎01-17-2020

Thank you both. Still struggling with the RANK function a bit. The first input in the function is the source data, which has a requirement of being a number, date, or time period. What should I point this first parameter to? Rob - in regards to your question: I am trying to ingest a data source that does not include a unique ID. This is going to come as a manual file load (for now), and therefore there is no ETL layer that I can leverage to source a unique ID. I'm OK with manually populating a basic unique ID in the source file (by just including a "row()" formula in excel), but need a mechanism by which we can clear and reload portions of the data as needed. So, I need an ID that I'll be able to replicate next time I load the data, and have Anaplan know which items to replace vs. add to the list. I was going to use the example above to accomplish that. My example was obviously very much simplified, and the real example would actually be a concatenation of SFDC opportunity and product. Hope this makes sense, and open to feedback. Thanks, Josh

jbrass · ‎01-17-2020

Hi All, I was hoping to get some with a problem that I'm trying to solve. I have a module with a line item that is list formatted. In this module, the 'applies to' is a list that has 7 values. The line item in the module has the following items shown for each of the 7 rows: item 1: Product A item 2: Product B item 3: Product B item 4: Product C item 5: Product C item 6: Product C item 7: Product D I'm trying to identify items that are shown more than once and create an additional snippet of text at the end to effectively make each item completely unique. The output I'd like to see is: item 1: Product A_1 item 2: Product B_1 item 3: Product B_2 item 4: Product C_1 item 5: Product C_2 item 6: Product C_3 item 7: Product D_1 NOTE: I do not want to set this up with a series of isfirstoccurrence logic that would require for me to setup a bunch of line items to accommodate any number of duplicate results. This logic needs to work with any number of duplicate results. Can anyone solve this for me?! Thank you!

jbrass · ‎10-31-2019

Hello, I constantly run into the same issue and I'm hoping that someone can help suggest a solution. When I create an action that imports from a csv file into a list or module, I always find it difficult to re-open up that import action and revise (or just view) the mapping that was previously created. Instead, I have to do this funky workaround where I re-import a file that has the same name as the file that was previously loaded only for the purpose of viewing the mappings. This seems silly to me; hoping someone has a better solution. Thanks! Josh