About DavidSmith

OK Try, where possible to push back on requests like this though.  We all know now, or should know, that we should avoid text formatted field whenever and wherever possible. This is where we have to get a little creative in giving alternative solutions to users, for the greater good!! David ... View more

@CallumW  Why do you need to change the Date to Text? David ... View more

All of the above is the reason why it is so important to bring dates in as date formats in the first place.  Not only do text formats take up more space and memory, but it is so much extra work to do to re-format. Try and unify on the format for all imports (e.g. YYYYMMDD) and use that for all dates to avoid this issue. Even for user driven uploads, it is better to add another column/row in the file to format the date in a "friendly" format David ... View more

@AlejandroGomez  thanks for this Understanding SUM and LOOKUP are the key to transformations in Anaplan, thus the foundational knowledge you need to build good models. We have focussed on this extensively in the new L1 training, with a specific lesson on it.  I have also attached a presentation I put together for CPX to help explain the differences and the techniques I use to get the mapping correct   With regard to TEXTLIST, the primary function is to produce a list of all of the difference text values.  So all the regions covered by a sales channel.  However, as @rob_marshall  mentioned, it is a very poor performing calculation. It is especially poor when used in conjunction with other functions such as FIND (e.g. territory assignments).  That is where we need to model differently and as mentioned, using two dimensional module with booleans is much more efficient.  As with other functions, and constructs, there may be times when you have to use them, but "use with caution" and don't use them if there is an alternative   I hope this helps clarify things David ... View more

@MarkT83  You can do this with a simple staging module 1. Assign currency codes to Regions 2. Add FX rates (I have assumed the base to be GBP) 3. Create a module by Region and Time to pull in the FX rate.  you need to do this to prevent the target module ignoring the LOOKUP (We found this as a recent bug!!) 4. Now you can use the rates from 2. and 3. to converted the input values I've broken up the line items for visibility of the calculation, but you can combine them obviously Global Base Rate = FX Rates.Rate[LOOKUP: Region Details.Currency] Region Rate = Region Rates by Year.Rate Converted = Local / Global Base Rate * Region Rate Converted Combined = Local / FX Rates.Rate[LOOKUP: Region Details.Currency] * Region Rates by Year.Rate   5. You can then view the totals by Currency for all Regions    I hope that helps David ... View more

@Ceri  The alternative to @usman.zia 's post is to calculate the converted amounts in a module by Cities_L3. You should have a system module at the L3 level in which you hold the mappings for L2 Countries and L1 Markets So in the L3 module you can use LOOKUP to pull in the FX rate for the respective Country   Then in the final Module by L1, you use a SUM on the L1 mapping to aggregate the converted data   Oh, and by the way, our best practice naming convention would be L3_ Cities, L2_Countries and L1_Markets   i hope this helps David ... View more

Agreed With links to release notes/anapedia pages/training videos David ... View more

I agree with all the points above Sometimes, you need to adjust the "process" to achieve the most streamline approach and educate/inform the user. Using a standard import process allows you to have more actions running as well as validations to ensure that invalid entries are not created (using a filtered view or DCA).  You can't stop the process from being run, but you can prevent items being adding during the process David ... View more

@karank  I'm not sure if this will help, but I've attached the sizing spreadsheet I use.  As I mentioned, this is pretty accurate.   I tend to use the "error" % to get the totals correct to what is shown in the model, and then use the increase row tot flex the sizing for what-ifs.  Certainly, that is pretty close to what I actually see when the lists are increased. Also, the "error" was pretty consistent initially for different sized models I hope that helps David ... View more

As we’ve said previously, you can’t use cells to Gb. That does not account for the Gb that lists and their subsets take up. I’ve been sizing a few models recently and when I use 500b per list item I’m only slightly off. I found that if I used 93% of the 500b for the lists, I was spot on. That worked for a 52gb model just as for a 2gb model David ... View more

@hina1994  Given it's a small model, why don't you create a module dimensioned by both Components and Products as a matrix.   And by the way, it is best practice to use a code for the numbered list and avoid using list properties, use a System module instead.   David ... View more

@Misbah    Just to clarify, List Properties  DO add to the cell count, it is just that the cells are not visible in the grid page of the list List Properties = Line items for size and performance.  Think of them as interchangeable David ... View more

@karank  You can't really use ratio metrics for cells to GB, it's too variable The sizing is as follows for any line item or list property.  The "cell count" for properties will be the number of members in the list (including subsets)   Boolean = 1b List, Time, Date formatted = 4b Number and Text = 8b   As @usman.zia said each list item = 500b (including the name and code properties)   You also need to account for subsets = these also count as list members too, so 1m items and 2 subsets of 500k each = 2m Finally you need to throw switchover into the mix.  This can reduce model cell count too as the "actual" periods are not counted   I hope that helps David ... View more

@obriegr  The challenge, as you've found, is display name is a list property so it is not possible to dimension this by anything other than the list. There is also no concept of a top level for the Users list   One thing that comes to mind, is to have a module dimensioned by the list and users where they enter their preferred name.  This might work if the downstream modules are view only; it is a technique we used to filter data before the users lists came out   Then in downstream modules, copy the 'Applies To', to all line items (replace the '-'). Now add Users to the module 'Applies To'.  This will set all line items to Subsidiary Views; hence why it will only work for view only Then add and dimension a line item to pull in the "name" from the first module. You need to do it this way to ensure the previous line item resolves; it is not enough to set it as a subsidiary view (because of the lack of top level) You'll need to set all other line items to exclude the users lists; so subsidiary views You then set the format width for the main list to 6, so it is effectively invisible     As mentioned, this might not work in all cases, but it is an option and will work for all lists, not just numbered, because you are ignoring the display name Alternatively, for cells you need to write to, you will need to dimension them by user; but that will increase the size of the modules I hope that gives you some food for thought David ... View more

I would also add, that even for experienced model builders, it is worth going through a lot of the videos as well.  We explain a lot of the new best practices that you may not be aware of.  It will add to the time taken, but you might just learn something new. David ... View more

@Tom_H  I often use the Current Period flag in Time Settings to help flag which periods are Actual.  This can be done in a Time Settings modules using START()<CURRENTPERIODSTART()   However, in your case where the actual periods may differ, I would set up an Time Lookup module (with no dimensions) and create a line item for each source, formatted as Month.  Depending on your logic, you may be able to make these formula driven (if, for example, Salaries were always a month behind Income) Then create a module by month (e.g. Time Settings by Month), with the three boolean line items e.g. Income Actuals Periods? with the formula START()<=START(Time Lookups.Income Actual Month)   You can then refer to these in the line item formulas   IF Time Settings by Month.Income Actual Periods? THEN Actual.Data ELSE Forecast.Data   If needed, you could also use Dynamic Cell Access to control input for Forecasts.  See this app for more details if you need to https://community.anaplan.com/t5/Learning/Dynamic-Cell-Access-Learning-App/ba-p/33869   I would ask, can you combine the three sources into one module?  If you use a list to hold the sources, you can save having three line items, and use the list in the above example instead   If not, you could always create a line item subset to combine the data sources and then use the line item subset as a dimension in the modules above See here for more details on Line Item Subsets https://help.anaplan.com/anapedia/Content/Modeling/Dimensions/Line_Item_Subsets.html   I hope that helps David   ... View more