Calculation Functions

Null value vs. True zero
Hi, I am trying to differentiate between a 'null' value versus an actual true zero (- vs 0) on a dashboard. Has anyone had any experience with this? I am looking at formula options to resolve this issue. Any help is appreciated! Thanks, Kaley
Previous year unitary calculations and summing
Hello everybody! I've come across a problem with making accurate calculations for a line item in currency/unit for previous year. My model has system version called "current" which has 5 years (2019-2023). Time options: months, quarter, ytd, FY. I'm making a module that is based on line item subset (included are: Volume in…
Error in List
This is the original list. This is my new List.
Memorialize Actual Data in Historical Planned Versions
Hello, I am currently running into an issue with one of my models where historical plans are changing due to actual restatements. I have a model with 4 versions. Actual, Budget, Working, and Current Forecast. Working is where all of the planning is done, and then that version is copied from working to current…
Formula to display values from the page selector in a column
Hi, I need to be able to display the page selector value in a column. The purpose of the module is to assign multiple positions to an order. If it had been the other way around (assign an order to position) I could have used an "assign" action, but, now he task is to do it this way. I need the order column to display the…
Issue with Percentage Number Formatting
Hi All, I noticed recently that the drill down ability on percentage formatted line items has been limited to the amount of decimal places / minimum significant digits selected for the format. Furthermore, if you drill into a cell that Anaplan is storing more precise decimals for, and then enter out of the cell, the stored…
How to Set search conditions for each user
Hi everyone. I would like to set search conditions for each user and I want to filter and display data. There are 110,000 items of data I want to display, and I want to specify search conditions for each user and filter and display them. Please tell me a good solution. Best regards. Taku
Reporting
This is the module I have. Now for the output is it possible to get the report of the module edited into the below view without editing the list name. I have no restrictions to create a new module or new list.
Identifying a Line Item's Max Value
In the attached example, I am using a rank function in the RANK line item to identify rows with non-blank ICM_ASNMT_ID and assigning them ascending rank values. Next, I want the MAX RANK VALUE line item to search through the RANK line item and return the highest value it can find (as demonstrated). In the example, this…
Return lowest date on set of data
hi I have 2 dimensions, one is Dates and one is accounts, and I want to return the first date for each different account where an amount occurs and on future line in that account How do i do this? they all have the same dates associated with them, but that date ins't necessarily the first date with an amount for each…
Is Parent Boolean
Hi, Is there anyway to have a boolean formatted line item = True if it is a parent line item? Example: Line Item A - Applies to: L1 - Format: Boolean - Summary: Formula L2 is the parent list to L1 How can I get only the parent checked as true?
Format of output
Hi Friends, I am having the data in this format. I tried it in excel but I am getting the output in the below format. Even making States as List Formatted Line Item didn't work. By any chance can we get the output in the format of Pic 1
count of projects within user access
Hi, I have a list of projects that is in selective access. I need to be able to get the count or number of projects per user. For example user A has access to 1 project. Total count of projects is 4. When I do a count line item (formula = 1 ) my line item result if 4 even if I only have access to 1 project. How will I be…
filter does not show correct total lines
Hi, I tried to do a filter to only display 2 lines with totals but the total lines are not showing correctly. Total should be zero. It is showing total of lines even if those lines are hidden. Can this be fixed? Please see screenshot below :
Sum with conditions
Hi all, I'm having trouble with the sum function. I have the reference module [A] as below: Then I need to sum in Module [B] that only select the "SGA" element in "P&L Total" (which is a list) but I can't. The line item needs to insert the formula is "SGA" in the image below: I've tried all sum, select, lookup, select&sum…
Pre-requisite for QUARTERTODATE() and HALFYEARTODATE() Function
Hi, I would like to calculate the QTD and HTD in a module. It is noticed that the QUARTERTODATE() and HALFYEARTODATE() function are not working when the Quarter Totals and Half Year Totals been disabled in the time setting. An error message is prompted when the formula is saved. After the Quarter Totals and Half Year…
Importing issue
Hi Members, Please help me in this import error. The original file: The original module: I am trying to fill the Role Column. Importing error.
How to find overall total
I can see subtotal but not overall total. Please assist me in this.
Issue with numbers as text
I am having the below issue with a number as text. it needs to be displayed as text, for posting into another system. in the attached, in IA local I am just using TEXT(ROUND('Investor Amount (Local)' * 100, 0) / 100) but as you can see on one line I get the number displayed in an odd format. I have tried rounding all over…
Mailto function won't open Outlook / Mail app
Hi community, In one of our models we frequently use the Mailto function. When I click the link, a new tab opens in chrome but Outlook doesn't open after. When my colleague clicks the link the Outlook does open. Pop-ups aren't disabled in my Chrome settings. Has anyone encountered this issue before? Thanks
Support of basic excel functions like RAND, RANDBETWEEN, RANDARRAY
It would be nice to have these Random number generating functions. These functions revery useful to generate some test and demo data, rather than inputting numbers and copy forward or importing from an excel. Can all be used for other purposes that I can not think of now.
Summary method shown when drilling down
I suggest a simple addition to the drill down feature that displays the summary method that was applied to the line item. This is because it is sometimes not readily apparent (especially to an end user) how a top level summation has been calculated.
How to make Date as a Display name in a Numbered list?
How do you make a Date as a Display Name in a Numbered list? I added a formula into my list properties with the format set as Date and the formula referencing the numbered list and the Date field. It pulled in the number properly to the Display Name. However, the new item created in the list is still a random number and…
LOOKUP does not work on list parents and only leaf items
When a LOOKUP is done on a parent item in a module, the result does not resolve to the column with that respective parent. Subsequently the value shows 0. To reproduce: 1. Create list with leaf item and parent 2. Create module with column 1 as "Data", column 2 as "Parent", and column 3 as "Lookup", using list from 1. as…
Parent-level Commenting with Line Items Pulled from One Module to Another
I have a question about parent-level commenting with line items pulled from one module to another. There are line items in module 1, which are pulled into module 2 via subset and transposed into rows. In the screenshot, you’ll see ‘Salaries & Wages’ is the sum of ‘Salaries’, ‘Overtime’, etc. There’s a ‘Key Highlights’ line…
Excel user defined functions
If my Excel model has user defined functions, ie multiline VBA functions that are called by worksheet cells, can Anaplan handle them? I could not find anything about them online thank you
Error : EMP02 - Dimension of mapping used for lookup doesn't match any dimension of the result.
Hi Experts, Please help on the below mentioned issue while adding formula for Bonus, Car, Phone costs & Medical costs to the EMP02 Module. BonusIF Employed? THEN Salary * 'EMP01 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country] ELSE 0Car costsIF Employed? THEN…
List Inheritance
Where lists are in a hierarchy, it would be really helpful if Anaplan natively understood the concept of inheritance. Here's an example. Consider two lists in a hierarchy: * L1 Countries * L2 Cities L1 Countries is the parent to L2 Cities. Within an attributes module, each L1 Countries list item is assigned a currency.…
Forward Weeks of Cover or Forward Weeks of Supply
Thought I would post this here as a method to calculate Forward Cover or Forward Weeks of Supply. A year ago @AnaplanIdeas posted an idea for this which is great. I'd like to expand on this idea and also incorporate @nathan_rudman backward-looking suggestion. Summary: In the supply chain horizontal you may be asked to…
Time Filter applies to specific month
Hi, I'm trying to create a time filter that will only ever return current month and December 2020. I've managed to create a filter for both using separate line items, struggling to combine and allow users to view. Any suggestions? Thanks.

Calculation Functions

Discussion List