Calculation Functions
Discussion List
-
Create a formula from text
I am trying to perform a lookup across multiple modules, but would like to avoid a gigantic case statement.I'll try to illustrate with this example. There is a list called details and a module called data. I'm trying to aggregate the data into a new module called summary. Right now, my formula is a massive conditional…
-
Find an item from a list with a property that isn't code
I saw the post about Find List Item based on a property, but the solution was the create a number list where the property for the Finditem was the code. We can not have it as a code, and they have similar role ups so the can not both be used as dimensions on a line item. How can I find the list item?
-
Summation using Months and Year
Hi All I have a piece of data which is based on unique keys. Each key has data for multiple months. Also there is another column which specifies the financial year. I have created an intermediate module where each transaction for a key is flattened out by month. So i have line items which derive the month value in text…
-
Find List Item based on Property
This is probably straightforward, but I'm struggling with it... I have a module that includes historical department names, which are combinations of numbers and text, i.e. "5100 Engineering". Sometimes the department names change, for example "5100 Engineering" might become "5100 Core Engineering". I want to create a list…
-
Pull/Lookup data from general list into module with numbered list
Dear All, Appreciate if anyone can help me on this. How to pull/lookup data (properties data of Total Area sqf) from a specific general list (Detail Project Component list) into a module (Master Tenancy List) that contained a numbered list with parent hierarchy is Detail Project Component? If not using numbered list, yes…
-
Sum to get total sales based on a GL Value by month
Hi, I am pretty new to Anaplan and i am hoping i can get help with a problem i am stuck at. Below is a mockup of my data Module 1Transaction 1Transaction 2Transaction 3Transaction 4Transaction 5Transaction 6Transaction…
-
Past 90 day functionality
I am looking for a rolling function that calculates the total value of opportunities over the past 90 days from the current date. Any suggestions or formulas would help! Thanks
-
Creation date for the record entered
Hello Need your help in finding way to populate the creation Date/time for the new record entered in the General List. Thanks Regards Prabakaran
-
When a data point extends beyond model's time range
I have run into an issue with correctly mapping monthly depreciation costs to the periods within the fixed asset's useful life. Module A (screen cap attached) contains the user inputs for the fixed asset including the depreciation start and end dates for the asset. In this module there are also two line items that use the…
-
If item is contained in list
I was reading this and on the EXAMPLE section, there is a IF condition as below; IF ITEM(Products) = Products.Widget THEN <... calculation for Widget ...> ELSE <... calculation for other products ...> This made me wonder, does Anaplan automatically compares each items within the list? In Python term, I am thinking Anaplan…
-
Does Anaplan have a "contain" function for words?
Hi, I am trying to create a module (line item) from a list formatted as boolean. Does Anaplan have a built in contain function so that I can select the words that I want ? i.e. if column 1 contains "SKY" then TRUE else FALSE Thank you!!
-
Convert a date from a TEXT formula to quarter
Hi there, I would like to create two new items for calculating “Renewal Date” and “Renewal Qtr”. I am able to figure out the calculation of “Renewal Date” but not the “Renewal Date” into quarter. Sales Type (Format: TEXT) Date 1(Format: TEXT) Date 2(Format: TEXT) The formula of “Renewal Date” is: IF Sales Type =…
-
Break data equally into Weeks from Months in 445 Distribution
Hello All, We have requirement where user will enter the numbers at Month level and the same has to be broken equally to the week level. All the rest of the data resides at Week level and hence the distribution used is 445. Is there any simple way to get this done. Need urgent help. ! Thanks
-
How to show the data directly rather than drill-down?
Hello, Background: I have a numbered list with a property NLIST Name: Genera_Special PersonType <<Property Name John Doe General Bill Gates Special Joe Neighbor General Steve Jobs Special There is another numbered list NLIST Name: Rich_Poor RichOrPoor John Doe Poor Bill Gates Rich Joe Neighbor Poor Steve Jobs Rich What I…
-
Need to use annual amount in a formula but comparing it to a monthly amount in same module?
I have a requirement to show the following: annual quota * multiplier = target then compare month orders to this target and see if the person exceeded the target. I have a module at a monthly level, and if you go down to the Quarter or monthly level, the quota keeps changing, i need this amount to remain static, and let…
-
Request for SUMProduct function
Currently only when you use Time you will be able to use PREVIOUS() or alike function. These functions doesn't apply to Lists but at CBRE I have a case where do need a similar function. Today the SUM function, which doesn't allow for Product, therefor my enhancement request to create a function alike Excel function…
-
Countifs Function
Hello, I've researched this topic but haven't been able to find a clear answer. I'm looking to create a headcount dashboard that tracks historical headcount by function over time (by month). It looks as though I'll need to combine an "IF THEN" and a "SUM" function. I am referencing a module that has: Employee Department…
-
Referencing Line Items from Other Modules/Lists (Performance Questions)
Is anyone able to describe the performance impact (if any) of referencing line items from other modules or lists which have the same relevant dimensionality? Particularly using SUM and LOOKUP operators. For example, a model may have a Register A and a Schedule B (over time), both of which have the same dimensionality…
-
Date Conversion
I have a line item that contains the date in a Text Format(examples below). Is there a formula that can convert these numbers to a Month Format? 2018001 2018002 2018003 2018004 2018005 2018006 2018007 2018008 2018009 2018010 2018011 2018012
-
Actual vs Last Year
I would like to store last year actual value in version. So my version willl have Actual, Last Year and Act vs LY Variance. Can I achive it using formula in Version setting? Thanks and regards, Maung Maung
-
How to get next item in list of Numbers?
Hello. There is a list of numbers (not number list) - 1,2,3 etc. There are no other dimensions except this list of numbers. No Time dimension. There are 2 line item: Lead Column and Cost Centres Could I put Cost centers to every row, like in a picture below? Thanks for Help.
-
How to display concat eliment of a list in different order
I have a list with values in the format "<description>-<code>" (seperated by -). I am displaying that list in one column of a module as a drop down from where user can select. I need to change this format of each value in the list like this "<code>-<description>". Can I do this using some formula over this perticular…
-
How to delete an list item that is specified via REST API?
We are trying to figure out how to delete a item in a list that is specified via the REST API. I have figured out how to create a control module that selects items in a list via a line item formula, create a Delete action that uses this control module, then invoke the Delete action from a REST client. (Each line item is…
-
STANDARD DEVIATION
Is there any function in Anaplan to calculate Standard deviation. In excel there is a function "STDEV". Need to get the standard deviation for a product across 36months of data.
-
How to set a boolean property for one (and only one) list item ... be like a radio button selection
I would like to allow a model user to flag ONE member of a list as the "base item". If the user changes a leaf-level list item's "base item" flag to TRUE, then all other leaf-level list items that were previously marked TRUE should be marked FALSE. One and only one item must be "base item" = TRUE at any given time. (Use…
-
How to aggregate the "same" month data from different years (Seasonality Calculation)
Dear all, We have a scenario which is to calculate the seasonality % based on the past 3 years' actual, and the seasonality is applied for the planing of the next four years equally. For example: FY18, January (Seasonality %) = FY19, January (Seasonality %) = FY20, january (Seasonality %) = ( FY14, January + FY 15, January…
-
Formula clarification required
Hi, Can anyone help me out on understanding below formula what exactly it drives ? Import Rebate Actuals.Gross Sales Amount[SUM: Import Rebate Actuals Map.Reporting GroupProductCustomer List, SUM: Import Rebate Actuals.Contract Name, LOOKUP: Reporting Contract Stage One.Contract Map] Thanks, Satish.
-
Conditional formatting on variance versions
Hi, I would like to apply conditional formatting on a dashboard. I have a module based on 3 versions. -Actual -Annual Plan (AP) -Variance Variance = Actual - AP Now the conditional formatting should be green if the Variance >0 and red when <0. However when I apply conditional formatting all versions will be formatted. I…
-
Circular Refference - using prior month value
Hi everyone, I'm having troubles creating a module that recalculates a regression each month. My regression is based on values of the last 12 months. When there are no 12 months of histicorical data available, I want the line item to use the previously predicted values with the regression as input for future values. This…
-
Long date format
Hi everyone, Apologies if this has been asked before but I couldn't find anything in my searches. Is there no way to have a 'long date' format for days, eg: dddd dd,mm,yyy Thanks in advance! KT
Upcoming Events
-
Unlocking the Value of Community with Anaplan Community Bosses
Join us for an exclusive professional development roundtable designed to celebrate, connect, and elevate our premier platform advocates: the Anaplan Community Bosses. The Community Boss program stands at the very heart of our ecosystem, highlighting leaders who dedicate their time to guiding peer model builders and…
-
Designing for Fit, Utility, and Practical Guardrails with AnaplanXL
Join Anaplan experts for a practical introduction to AnaplanXL (AXL), the Excel add-in that connects Excel grids directly to governed Anaplan data. As the first session in our technical series, this event is designed to help: Model Builders understand the fundamental purpose of AXL: what AXL does, the basic guardrails for…