How to get Min/Max and Today's date in Anaplan
|Saturday, 2 May 2020||100||1000|
|Sunday, 3 May 2020||200||2000|
|Monday, 4 May 2020||300||3000|
|Tuesday, 5 May 2020||400||4000|
One of my favorite topics is data integration and in particular the management of dates and times.
So, thanks for asking this question!
To begin with, I'm not aware of a simple formulaic way to get the min and max date into a calculation module, perhaps with ISFIRSTOCCURANCE() you can try.
Frankly, I just prefer to use the RANK function and use import actions to populate my time system modules.
In this case we'll have two modules and three import actions:
- transaction module (note: if you dimensionalized time in your transaction module - which is best practice - then you'll need a system module)
- A time system module that holds our important dates
- two import actions to get the min and max
- one import action to get the current date
First, build out your transaction module
Blueprint: Don't forget to turn off summaries for the rank functions.
Next, build a time system module to hold our important dates. Note: To import the current date from outside Anaplan you may need to add a dummy dimension. Let me know if you need help with that.
Then build two actions that import the min and max date into your Important Date module
Since Anaplan does not have a TODAY() or NOW() function, you will have to import the current date with a scheduler or enter it manually.
If your module that holds the current date has more than one line item, you will need to add a dummy dimension.3
If the transaction list has a top-level you can use a summary method of min/max on the original line and then add a second line with the other summary. For large transaction modules this will reduce the number of line items and avoid issues with the RANK() formula after 1 million (performance) and 10 million (artificial limit). If you only have one day per line then it shouldn't really matter as you would be unlikely to hit these numbers.
To get a list count, depending on what you are trying to accomplish, you can add a line item to a module with the formula = 1. This would require the top level again but if you have that you can grab from the top, otherwise you could use a SUM against it.3
I personally try to avoid actions when possible so I'll suggest another way:
You can create two new line items with the date and set their summary to min / max
You can then easily extract this value into any other module by doing:
module name.Date Min[select:list.top level]
To get the number of elements in a list IN a module, you should have a line item called count (with a formula = to 1) in a system module. The top level of the list should now reflect the number of items.2