Calculation Functions

Why is ramp format a number and not percentage?Am trying to appreciate the formula and output on this line item.

i'm using three versions in my module, here Actual, Forecast, Budget. i have the actual and budget data available and they can be manual entry data. Forecast data should be formula given. Actual Budget Forecast Jan Forecast Feb ---- until Forecast Dec Thank you,

i have a question, on my project i have many versions such as Actual, Budget, and Forecast(Jan-Dec) . on Actual and Budget version will input by user but for Forecast this will retrieve data from another forecast module version? how is it done?

I recently posted a question on the forums in relation to a workaround for a bug in the LOOKUP formula. I wholeheartedly feel like the LOOKUP bug (where it doesn't work for parent items that are in the same list as the leaf-level members) should be fixed. And I recognize that there are some workarounds available (as…

What is different about LOOKUP and SUM?

Hi All, In simple terms I want to know the total till the previous rank complicated by the fact that the last number depends on the rankcumulate till previous rank. Hence this is giving rise to Circular references. Let me give an example (see attached) * I have sales volume of SKU by date dimension ( though this data is…

HI Experts, We have line items Line item:1 : FCST_1 Line item:2 : FCST_2 based on FCST_1 convert into Rounded value. Both line items are text format. First item data in dot(".") format but we need to convert comma(",") And we need round off the FCST_1 line item. For suppose FCST_1 = 1.2498 then by using some formula we…

Having the option for user input at a parent level in a module would be very helpful. For example, our users are required to leave commentary on their monthly variance to actuals. Currently, they are only able to do this at the bottom level of our Cost Center hierarchy, but many of them would also like to leave commentary…

how to create best formula for using IF ELSE?

Hi, I need to map high level data (Product) to leaf level (SKU) for exporting planning information to transactional systems. Each product has multiple SKUs so we'd like to automate that mapping by using the first SKU within the hierarchy. I can calculate the SKUs sequence within each product using RANK function but I'm not…

Hi, I am trying to create a boolean flag that will tell me if a month has 5 saturdays or not. Can someone guide me in constructing this with date functions? Unfortunately, I cannot change my calendar settings to 4-4-5 etc because of business restrictions, so I have to calculate this manually in order to adjust and smooth…

I've entered this formula for the goal calculation. IF Active? THEN ROLE GOALS.Quota Goal[LOOKUP: Region, LOOKUP: Role] / 12 ELSE 0 I'm getting this error. Dimension of mapping used for lookup doesn't match any dimension of the result Has anyone else run into this?

Hi everyone, Capps Europe and Advanced AnalytiX have made a video on how to use SUM, LOOKUP, and SELECT functions in Anaplan. The way these functions work is quite different from Excel, and hence they may often be confusing for beginner Model Builders. I hope you will find this helpful. The video is available here.

Thanks in advance for the adivce... I am trying to push a boolean selection from one module to another. Let's call them input module and calculation module. The input model sits at a hierarchy several levels above the detail module. Input Module has not time version or time dimension and sits 3 levels above in the…

Hi, I have a list "Aging Buckets" with list items like below, and 2 properties (Range Min, Range Max): Range Min (Property1) || Range Max (Property 2) Bucket 0 0 1 Bucket 0_30 1 30 Bucket 30_60 31 60 Bucket 60_90 61 90 Bucket 90_180 ... .... Bucket 180_360 181 365 There is an "Average" module which is dimensioned ONLY by…

I have a module called Sales: dimension is a list,Employee-Product Groups. Line Item is Units Sold and Time Period (MONTH). I want to create a module with 2 Dimensions , Employee-Product Groups and TIME, and the rest of the line items. Please help.

Hi, Anaplaners, I am struggling with how I can retrieve Time Period where num1 matched to num2 (please see the attached file for the detail) Thanks in advance for your help (cell colors in the file do not intend conditional formatting)

Hi, I am looking to count the number of layers (not descendants) in an employee hierarchy. For example: Manager 1 has 3 direct reports Mananger 2 reports to Manager 1 and has 4 Direct Reports Manager 3 reports to Manager 2 and has 1 Direct Report The result would be: Manager 1 has 3 levels Manager 2 has 2 levels Manager 3…

For example, consider the following hierarchy, applied to a module, with the following values: Net Income: $10 Gross Margin: $40 Sales: $100 Cost of Sales: $60 Operating Expense: $30 Clients would prefer to see Cost of Sales as a positive number. In aggregating, they would want to see Cost of Sales subtracted from Sales to…

Hi, My current module has Year as the time dimension and my reference module has months as the time dimension. I want to return a field from the referenced module but only for the forecast period (Current Period is set to Jan19) & hence for FY19 that would be the sum of Jan19 to Jun19 and FY20 Jul19 to Jun20. My formula is…

This might be a silly question, but I just got stuck and couldn't think of a solution. I want to create a new line item showing the max of an existed line items. Take the example below, I want to create a new line item called max days, which takes the max of Days. Original: Days A 1 B 2 C 3 D 4 New: Days Max Days A 1 4 B 2…

The ability to use what is selected in a page selector in formulas and filters would be very useful. Although you can create a custom filter with a cell acting like a page selector, if multiple people are on the dashboard and using the cell, it can cause confusion. 

Hi, I am trying to sum a column within a module on open balances for all of my bill-to Customers. The reason for me doing this is I have them by aged deductions and then I used the SUM function onthe aging buckets to get the total of that particular customer. Now I would like a calculation that tells me what the percentage…

For a line item that has a saved value that is negative (take net income for instance), a less negative or positive value to compare to should be green, but is highlighted as red when you use color coding with the compare tool in Anaplan. Has this issue been identified before? Seems like it would be an easy fix since in…

Wizard to help Anaplan business end-users to write-down formulas and create objects on models. This wizard such as an Avatar could be the AnaFriend indicating the best function or best way to use a specific function or Anaplan models objects such as list, subset or module.

Hi Anaplaners, I would like to figure out numbers of months elapsed since consecutive zeros have started. (please see the attached). For instance, in the attached file, as sales of product BBB is zero since July 2019(instead of April 2019), I would like to get "4"(months) given current period is Oct 2019. Thanks in…

Giving different sections of a formula different colors to organize while writing it. Especially when writing a longer nested-IF formula, it would make the organization of the formula easier as well as decrease opportunities to stall the model.

Hi All I have an audit module which has a list as dimension. The list contains multiple report names. The List is pivoted as rows and line items has 2 columns as Count and Total_amount. My need is to go to respective source module of the reports and fetch the total count of records in that report and sum(Total_amount) in…

Hi, Anaplaners I posted a similar question before (https://community.anaplan.com/t5/Best-Practices/Time-where-a-value-is-max/m-p/40577#M4185), but this time I have faced an issue that cannot be solved by the solution I get to received in the previous question. Thing is that (see the attached) the line item "MAX Value" see…

Hello, Am trying to make a calendar in Anaplan to plot weekdays and holidays. Time dimension for the model is "DAY" and I wanted not to type the dates in each days, but to put a formula instead. What would be the formula to return the "date" of the time dimension to the line item. I tried to use "ITEM(TIME)" but it does…