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

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.
 
DateCustomer countRevenue
Saturday, 2 May 20201001000
Sunday, 3 May 20202002000
Monday, 4 May 20203003000
Tuesday, 5 May 20204004000
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.
Tagged:

Answers

  • @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

    MinMaxDate003.png

    Blueprint: Don't forget to turn off summaries for the rank functions.

    MinMaxDate004.png

    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.

    MinMaxDate005.png

    Then build two actions that import the min and max date into your Important Date module

    Max Date

    MinMaxDate001.png

    Min Date

    MinMaxDate002.png

    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.

     

  • 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.

     

    johndorma_0-1588864997152.png

     

  • 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

    nathan_rudman_0-1588865059345.png

    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.