-
Period on Period % Mvmt - Formula Summary Method
Hi Team, I'm attempting to create a Period on Period % movement in a module using multiple dimensions but I require the summary level/top level to still calculate using the formula rather than Summing all the lower node %'s. When I change the Summary method to % I receive an error I have tried using Period, Lag & also…
-
Extract FY from Date
Hi Team, I'm trying to extract the FY from a date. In our world the Fiscal years run from July to June & hence 1/07/19 would be FY20 I could convert the date to a Time Period & have a separate module that maps the Time Period to FY but I thought that might be overkill? Many thanks Mark
-
Displaying the first date of my historical years
Hi All, I was wondering if there is a syntax that gives me the first date of my historical years, based on the timescale settings. My Fiscal Year (currently) goes from 01-06-2018 to 31-05-2019 and I have 2 past years within my model, i.e. the date I'm looking for right now is 01-06-2016. I would like to have a syntax that…
-
Formula to populate time period formatted line item
Hi, I have two line items - Year and January - with no dimensions. Year is time period (year) formatted and a user selection January is time period (month) formatted and I am looking for a formula which uses the year the user has selected to return the period - For example, the user selects FY19 and the it returns Jan 19.…
-
Truncate decimals but not round
Hi All, I want to truncate the decimals from a number like below but do not want to round it. 2.22939393 -> 2.229 2.22977777 -> 2.229 2.59999 -> 2.5 instead of 2.6
-
I need some help on formulas and functions in anaplan where can I find some relevant material
I want to practise formulas and want some deep understanding
-
ACTUALVERSION()
We currently have the functionality to retrieve the values from the Current Version using CURRENTVERSION() Please can we have ACTUALVERSION() to do the same for the Actual Version This provides consistency and will simplify the modelling experience David
-
Variable results on dashboard depending on user selection
I want to present a dashboard where the information displayed varies depending on the year/version the end user selects - see attached picture of example. The modules that lie behind this example would be as follows: Input-Module 1 Input Cost - user input (Dimensions - Location List, Version List, Year) Calculation-Module…
-
need to do a look up formula
Hi, I need to do a lookup in my line items so that I can import the data into a module. I have a flat hierarchy factory list. I have 2 line items Inbound Qty and Lot Numbers. I've split them so that I can see: Factory 1 inbound Factory 1 Lot Number Factory 2 Inbound Factory 2 Lot Number I'm not sure what formula to use in…
-
Optimizer Constraints
Hi, In the constraints we specify for optimizer, the last constraint has the format of a - b =0, why is a=b not possible in the constraints. If we change the format , the process will be incomplete. Why is it so.??
-
Formula to get name of a boolean formated line item .
Hi, I have a module a with several line items. Line item 1 of this module is boolean formated and applies only to VERSIONS. Means, a User has to mark the check box of the VERSION, he wants to use for further steps. In a second module b, there is a line item 2, which shall show the Name of the VERSION, which was marked in…
-
Time Dimension Summary
Hi Team, I have noticed that when referencing a module that has Time as a dimension from a module that does not have it as a dimension that the formula will not return the highest node in the Time Dimension E.g. I have a Module with Employees & a Boolean line item and a Module with Employees & Time (with Summary as Sum) &…
-
Drill to transaction on module with versions – tip
Hi everyone, I thought I'd share a something useful I recently discovered, which I haven't seen documented anywhere. According Anapedia, Drilll to Transaction is only supported for formulas like TransactionModule.Amount[SUM:___, SUM:___] So this means that you can't drill to transaction on a formula like IF…
-
Refer formula across modules with different time range
Hi, I have two modules named A and B. In module A, my time range is FY17 & FY18. And in my module B, the time range is FY18 & FY19. The fiscal year in my model is Jan to Dec. Now I want to refer the data of FY17 from module A to FY18 of module B. I have tried with Lag, lead and offset. But all these have the restriction as…
-
Matrix of Conditions
Hi Team, I'm trying create some logic whereby I classify each Customer Account as either Existing, New Business or Churned for each Financial Year based on whether they had revenue in the last month of the previous financial year compared with the current year I have created the Matrix below (the columns are Line Items)…
-
Spreading amounts using Normal Distribution
I want to add a feature to my model that will include these inputs: Start Date Number of Months Amount Then I want the model to spread the amount across the months using a normal distribution (bell curve). In Excel, I have used the NORMDIST function to calculate the probability be period and then multiply that against the…
-
Create Function for ADDWEEKS
Please could we have ADDWEEKS as a function to work the same as ADDMONTHS and ADDYEARS the current workaround is to convert the weekly time period into a date then add or subtract 7
-
Sort List for Page Selector
Hi team, Is there a way that I can display a list in the Page selector based on the sorted order from another module? I have a Customer List that I am using as a Page Selector in 1 module and wish the List to be sorted from Highest to lowest revenue gap (it's a query to a employee) which I have created in another module…
-
Median Calculation/Exclude NAN from MAX function
Hi team, I'm attempting to calculate the Median revenue per customer over a period of 4 months (excluding negative revenue months due to credit notes etc) but cannot obtain the Max value per customer due to it returning the NaN value (if there is 1) My process has been: 1) Module dimensionalised by Customer & a dummy Month…
-
IRR - returning NaN
Hi, I have a module with below dimensions. I need to calculate IRR for additional planes. I have followed the suggestions and created a transaction module with the following dimensions (Schedule, Aircraft, Airlines, Time month scale, versions). It is not returning a valid result in below module. Any suggestions that would…
-
Employees per Team
Hello, I have this "Employee Profile" module with dimensions Time and Employee list as shown below: Immediate Superior Line item is a list-formatted line item, that uses also "Employee List" as format. Is it possible to make another module using this data, to show all employees reporting to each Immediate Superior every…
-
Matching
Hi, Please see attachment Regards,
-
Formula Summary Method for List
Hi, I have module with two lists L6, Metrics List and one line item L6 List is parent child hierarchy list Metric List is flat list with one parent (Total Metrics) Source(Line Item): ITEM(L6) The Ask is, In Source(Line item) values are not getting populated at Total Metrics Level Thanks & Regards, Asha
-
FIND MULTIPLE DELIMITERS
I am trying to take a 3 part Unique ID with delimiters and separate it at the delimiters. I can achieve this for the first 2, but not the last part. ex: 123-0542-236541 LEFT(NAME(ITEM(LIST)), Delimiter in Item Name - 1) = 123 RIGHT(NAME(ITEM(LIST)), Delimiter in Item Name) = 0542 Delimiter in Item Name = FIND("-", Item…
-
Display message on Period Level
Hi I have module which shows variance of data populated by month this module is published in the Dashboard. and a Text message in the dashboard is displayed "Having variance" or " No Variance".(This is a different module) which works fine based on data available or not. Problem here is, for example when 'FY17' or 'All…
-
Need formula help to calculate YEARTODATE -1
Hi, I have a need to calculate YTD Actuals but the amount should show the sum of values from start month (Jan 19) to One month prior to the current period i.e (July 19). The current formula I have is this: YEARTODATE('P&L by Cost Center'.Amount Adj[LOOKUP: Version Selector.Actuals]) I need to pull YEARTODATE just until end…
-
Formulas Not Adhering to Subset Formatting
I believe that I have found a bug, but wanted to check this by the Community to see if I'm missing something or if there are some additional creative workarounds that we could employ in the future. I've created a simple illustration of what we are encountering for the screenshots below, but what we are doing in reality is…
-
Removing Duplicates with custom conditions
Hi I have a requirement to delete duplicates with some custom conditions. Consider the Element "occurrence" in below image for your reference, I need a formula to automate this boolean element. Conditions are as follows 1.First two elements with first data "11" has Second Data "0" and "1" , In such a case , we need to…
-
Question on Maturing inventory
Hi Everyone, i’m hoping you might be able to help me, I’m looking to build a model which takes an inventory with a start date and then shows when it comes of age e.g. beef that needs hung for 28 days, or wine that needs stored for a couple of years. I’d know the current demand but I’m not sure how Or if I can get the…
-
Use Time Ranges in formulas: Make the Time Range a named member in the Time Dimension
If Time Ranges were true named member in the TIME dimensions we could easily control formulas and filters based on a time range. It would also allow for YoY changes in formulas to be maintained easily w/o having to create multiple line items. Many companies need to save off older versions for compliance reasons. Adding the…