About peter_mcanena

peter_mcanena · ‎04-22-2018

Yeah that's a good way to do it but it'll only work with Anaplan Connect. I sometimes need to have a user-activated process that works this way.

peter_mcanena · ‎04-10-2018

Hi, I am trying to create a list as shown in the attached image. Here i have'nt created the seperate list for 5804 (which is like parent list). To avoid sparsity & size constrain, i am trying to create parent & child list in the same list. I know how to create in the tool for less items. Can someone tell me how to use Import data to create such list with bulk data. Thanks, Sangeetha

peter_mcanena · ‎03-16-2018

This is really helpful for reading formulae but you should never copy the formula out of the drilldown window for editing. If you do that (as some commenters have noted), you lose some brackets. This is because the drilldown window uses indentation to show the nesting of, e.g. IF statements, rather than brackets, which are less readable but absolutely required for formula editing!

peter_mcanena · ‎02-27-2018

Hi Nathan, Yes, this is a neat solution. It would be great if you could do the whole thing without resorting to the time dimension though! Pete

peter_mcanena · ‎11-08-2017

Referring to the previous item in a list is a nightmare: You can write a lookup to find the previous item, but as soon as you try to use it you get a circular reference. Sometimes you can get round this with creative use of the CUMULATE() function on your list, but sometimes you can't and you need to map your calculation onto the time dimension. But if CUMULATE() can be used on a list, why not PREVIOUS()? It would be awesome to have a PREVIOUSITEM() function that allowed you to pick up a value from the previous item in a list, while letting Anaplan know that you're not creating a circular reference. You can be sure it's not a circular reference because the dependencies would always flow in the direction of the list, and it would solve all sorts of problems. We're always saying "If you can do something in Excel, you can do it in Anaplan," but recursion is an example (possibly the only example?) of something that's simple in Excel but virtually impossible in Anaplan. Excel can handle recursive calculations on any dimension, but Anaplan can only really handle recursion over the time dimension. This would solve that. PREVIOUSITEM(x, y) where x: Any expression y: List The expression x must be based on the list y. PREVIOUSITEM(x, y) would then pick up the value of x from the previous item in the list y. Anaplan would need to allow PREVIOUSITEM(x, y) to be used in the definition of x itself, in the same way as PREVIOUS(), without creating a circular reference.

peter_mcanena · ‎05-10-2017

Thanks Brendan!

peter_mcanena · ‎05-05-2017

In the old community you could click "latest posts" (or "recent posts" or something) and see the most recently posted questions. This was great because you could see new questions and answer them straight away. Now there's no way to do this (or no obvious way) so presumably people's questions are going unanswered because nobody can see them! Or am I missing something? Is there a way to see the latest posts in the Community?

peter_mcanena · ‎05-02-2017

Hi all, This looks like a bug in Anaplan (and it's still happening as of today). However, you can get round it by using a different syntax for the ROUND() function. Instead of rounding to 2 decimal places, multiply the number by 100, then round to 0 DPs, then divide by 100. So the original example becomes TEXT(ROUND(2.509 * 100, 0) / 100) which returns "2.51" as expected. Hope this helps. But Anaplan, please fix this bug! Pete

peter_mcanena · ‎05-02-2017

You delete an Abb Hub model in the same way you'd delete any other model: Drop down the menu under your name & select Manage Models, then select the model you want to delete and click Delete.

peter_mcanena · ‎03-07-2017

To calculate a sum-product like SUMPRODUCT('Line Item 1', 'Line Item 2') you need to define an intermediate line item for the products, then sum that. So you'd define a new line item called Product, with the formula 'Line Item 1' * 'Line Item 2' Then your sum-product is the total of the Product line item. The same technique works if you've got three or more line items for your sum-product.

peter_mcanena · ‎03-07-2017

Hi Rumi, The following formula should work: FINDITEM(Account, LEFT(Concatenated String, FIND("_", Concatenated String) - 1)) This finds the section of the concatenated string up to the first underscore, then looks that up in the codes of the Account numbered list. It will work as long as you've got codes in your Account list, and "12345" is the correct code. Hope this helps, Pete

peter_mcanena · ‎02-20-2017

Hi Benson, You can import the file into a module blueprint, but this will only import the line item names and codes, and any parent-child hierarchy if there is one. To do this, go into the module blue print and click Import. The interface from there is just like any other file import. Once the line items have been added, you can copy & paste the other information into the bluerint (Ctrl+C, Ctrl+V). This is very quick if you only have a few modules to process, but it will only work if you have the formats of all the line items. Paste the formats first, then then formulae, then the summary methods. If you haven't got all that information you may not be able to recreate the module at all. You can only import one module at a time this way, and you'll also have to create the modules manually first! I don't think there's any way to recreate a whole model in one go. Pete

peter_mcanena · ‎01-30-2017

The formula works for me - see attached screenshot. The explanation will be one of the following: The text in 'Text 1' doesn't match "Kostenverdeling" or "Registered" exactly, either because there's a space or something at the start, or because there's a spelling mistake in the underlying list Your formula is case-sensitive but "Kostenverdeling" is actually coming out as "KOSTVERDELING" or "kostenverdeling" etc. If you want a non-case-sensitive formula, use the COMPARE function instead The second part of your formula is failing because there's no comma in 'Text 1'. If this isthe case you'll see empty strings in your result To diagnose the problem, add some temporary line items with formulae like IF LEFT('Text 1', 15) = "Kostenverdeling" THEN "K" ELSE IF LEFT('Text 1', 10) = "Registered" THEN "R" ELSE BLANK and MID('Text 1', FIND(", ", 'Text 1'), 100) This will help you work out which part of your formula is failing. Pete

peter_mcanena · ‎01-25-2017

Hi Yashasvi, Are the line items formatted to the same numbered list, or two different numbered lists? If it's the same list then you can just say IF Module 1.Line Item 1 = Module 2.Line Item 2 THEN ... ELSE ... If they're two different lists then you need to decide how to do the comparison. Typically a numbered list will have some list properties that define the elements in the list. If your two lists are comparable then they should have some of these properties in common - let's say there are two properties Property A and Property B - so you can compare the two line items like this: IF Numbered List 1.Property A[LOOKUP: Module 1.Line Item 1] = Numbered List 2.Property A[LOOKUP: Module 2.Line Item 2] AND Numbered List 1.Property B[LOOKUP: Module 1.Line Item 1] = Numbered List 2.Property B[LOOKUP: Module 2.Line Item 2] THEN ... ELSE ... The list property you want to compare might be the display name, in which case you can say IF Numbered List 1.Display Name[LOOKUP: Module 1.Line Item 1] = Numbered List 2.Display Name[LOOKUP: Module 2.Line Item 2] THEN ... ELSE ... Or if you want to compare the codes, then you need to say IF CODE(Module 1.Line Item 1) = CODE(Module 2.Line Item 2) THEN ... ELSE ... Otherwise, if the properties you want to use to decide whether the two items are the same don't exist, then you'll need to define these properties and then compare them as above. Hope this helps! Pete

peter_mcanena · ‎01-18-2017

I agree completely! In fact, I'd go further and say that all internal imports should be from views. You can then make changes to the views by adding filters or additional columns if you need to. You might not need any filters right now but how do you know you won't in future? Since you can only have one import from each module into each target (list or target modules), you'll need views for each subsequent import. You'll be able to add filters to these imports (because they're based on views), so why prohibit yourself from having filters on the first one?

peter_mcanena · ‎01-18-2017

You're welcome! An additional tip: create a model dimensioned by built-in time to populate your custom hierarchy, with lookups to check which months & weeks need to be added. Then you can write a process to add missing periods & your hierarchies will always be in synch.

peter_mcanena · ‎01-17-2017

Hi Natalia, You can achieve this result by creating a custom time hierarchy. You'll need a list for years, a second list for months, with years as the parent, then a third list for partial weeks, with months as its parent. The partial week list should have a property mapping it back to Anaplan's built-in time period (i.e. the start or end date of the week) and a weighting, which will be the multiplier for how fully the week fits into the month (i.e. 100% for a complete week, 6/7 = 85.7% for a six-day week, 5/7 = 71.4% for a five-day week and so on, down to 1/7 = 14.3% for a single day). You can then write your module dimensioned by the partial weeks hierarchy and get the data from the source modules (which are dimensioned by the built-in time dimension) by looking up the start/end date of the week and multiplying by the weighting. Hope that helps, Pete

peter_mcanena · ‎01-06-2017

This is an alternative, but you should be careful because it doesn't actually change the result; it just hides it. If you add another line iten checking if the result is = 0 then you'll see that it still contains a small rounding error. The only complete solution is to use ROUND.

peter_mcanena · ‎01-06-2017

Yes, I understood that it was hardcoded. But the answer is the same: Anaplan will sometimes produce small rounding errors, which you can either hide by using the "decimal places" format that Gaurav suggests or get rid of completely by using the ROUND() function as I've suggested. The same applies to other technologies, not just Anaplan. This is particularly important where you have a check later that says IF x = 0 THEN ... because if x contains a small value the check will fail, even if you've hidden it with your "decimal places" formatting.

peter_mcanena · ‎01-05-2017

Hi Jay, The problem is that there are tiny rounding errors in each step of your calculation. Sometimes these rounding errors cancel each other out, sometimes they add up, and that can depend on unpredictable factors such as the nesting of brackets in your formula. You may have observed the same phenomenon in Excel. In your case the error is 7.276E-12, which is then that one billionth of a penny/cent. The solution is to add rounding to your formulae, to give you the accuracy you require - say two decimal places. So instead of ClearAB: A + B - 45250.4 - 65810.4 ClearABAlt: A + B - (45250.4 + 65810.4) you'd have ClearAB: ROUND(A + B - 45250.4 - 65810.4, 2) ClearABAlt: ROUND(A + B - (45250.4 + 65810.4), 2) This will give you the expected zeroes.

peter_mcanena · ‎01-05-2017

Can you post your blueprints so we can see exactly what's going on?

peter_mcanena · ‎12-16-2016

Hi Jaime, You can get fix that easily enough if you know where to look. If you change the Summary method of the Tax Rate line item to "Ratio" and select the appropriate numerator & denominator, Anaplan will calculate a weighted average tax rate at the upper levels of the hierarchy. Let me know if that works for you. Pete

peter_mcanena · ‎12-13-2016

Concretely, what you should do is this: Pick any list that's part of he dimensionality of your module. Let's assume you choose one called Product Define a subset of that list, called, let's say, Product: None. Don't add any members to the subset In the Applies To for your module, replace Product with Product: None. The cell count of your module should drop to 0 Now change the Applies To back to what it was (replacing Product: None with Product). The cell count will return to what it was The blue cells in your module will now be empty If you have any line items with different dimensionality, you'll have to change them as well (unless they contain formulae).

peter_mcanena · ‎12-12-2016

One way to do it would be to change one of the dimensions to an empty subset of the same dimension, then change it back. That will preserve the dimensionality and it won't break any modules that refer to the data, and it will set all the data to zeroes, blanks, false, etc. If it's something you want to do regularly you should set up an import action ot import a blank dataset into the module, and select Clear Target on all dimensions. That will also clear out the module.

peter_mcanena · ‎12-08-2016

You can prorate the opening month like this: (END() - Agreement Start + 1) / DAYS() And prorate the closing month like this: (Agreement End - START() + 1) / DAYS() ...assuming that Agreement Start and Agreement End are the first and last days of your agreement respectively. These formulae give you a pro-rata multiplier to apply to your rent or whatever.

peter_mcanena · ‎11-29-2016

Hi Leo, You can't reference a page selector in a filter, unfortunately. You might be able to get the functionality you want by adding a Boolean line item dimensioned by the same list (as well as everything else in the module's dimensionality), then putting a formula in that line item that checks if the row you're looking at has that item in the filter. You could then put your filter on that line item instead of the list-formatted line item, and it will pick up its context from the page selector and change the visible rows accordingly. Be careful this approach doesn't create a size problem for you though. Pete

peter_mcanena · ‎11-24-2016

OK I see. In that case you need some intermediate line items: Periodic Previous Month Periodic Previous Quarter YTD Previous Month YTD Previous Quarter In the Previous Month line items you put the formulae you've currently got in Previous (i.e. LAG(Source Module.Periodic , 1 , 0) for Periodic Previous Month), and in the Previous Quarter line items you put the same formulae but lagging by three months instead of one (i.e. LAG(Source Module.Periodic , 3 , 0) for Periodic Previous Quarter). Then in Periodic Previous and YTD Previous you can decide which of the two to pick up by saying IF MONTH(START()) = MONTH(END()) THEN Periodic Previous Month ELSE Periodic Previous Quarter and IF MONTH(START()) = MONTH(END()) THEN YTD Previous Month ELSE YTD Previous Quarter respectively. You can now change the summary methods of these line items to Formula, and Anaplan will pick up either the previous month or the previous quarter as appropriate. I've attached the full blueprint in case this isn't clear. I've got it working using the attached.

peter_mcanena · ‎11-23-2016

Hi Mathias, Your formulae are all fine; the problem is your summary methods. If you change the Time Summary for YTD Actual and YTD Previous to "Closing Balance" then you'll get the results you want. To do this, go to the blueprint and click the ellipsis in the Summary entry for the YTD Actual line item. The main Summary is probably already to "Sum", which is fine; just change the Time Summary to "Closing Balance". Then do the same for YTD Previous. Hope this helps, Pete

peter_mcanena · ‎11-21-2016

Hi René, Filters don't work very well when you've got Line Items on rows. That's because different line items can have different formats (some may be numeric, some boolean etc.), even if in your case they're all the same format. If the line items in question are all numeric, you could get round it by creating a line item subset, then putting that on your rows along with WBS Elementen, and Time on columns. That would need to be done in a separate module though. Pete

peter_mcanena · ‎11-18-2016

Can you see them now?