How to get Min/Max and Today's date in Anaplan

mohitveer_sandhu

Hi,

 

We are trying to get the Min/Max and Today's date for data in the Anaplan Module. Below is the sample data present in the module.

 

Date	Customer count	Revenue
Saturday, 2 May 2020	100	1000
Sunday, 3 May 2020	200	2000
Monday, 4 May 2020	300	3000
Tuesday, 5 May 2020	400	4000

We expect to get 2nd May as Minimum Date, 5th May as Maximum date and 7th May as today's date. Please Let us know how we can get these 3 extracted.

 

Also, could you help with how we can pull the number of elements present in a list.

JaredDolich

@mohitveer_sandhu 

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

 

Example:

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

Max Date

Min Date

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.

 

johndorma

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.

 

 

nathan_rudman

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.