Contributor

## 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 account.  And i want the resulting first date to be on every line for every account on each dates, as i need to test against it.

I thought i could do a rank function on the dates, where amount doesn't equal zero, so that gives me the lowest number for each account, which corresponds to a date, but am unable to get that lowest date on every line and not totally sure how to convert the rank back to a date?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Boss

Here's what I did:

The line items from Year Number to Day Number are an example of how to convert the text of your transaction to a date. In the real world, I would advise not to do it in this module but in a List Management XXX: Transactions module or even ideally have it loaded as a date format when you create the list. Anyway I put it here in case you need it.

The logic you're interested in here is the date line item. its formula is as below and its summary type is MIN

``IF amount <> 0 THEN Real Date format ELSE DATE(2099, 12, 31)``

Anaplan will consider an empty date to be the lowest, so you have to put a very high date so that it will be ignored.

Now the final line item is simply:

``Date[SELECT: 'forum: Date list min date'.All Transactions]``

It take the value of the Date line item at the summary level, which is set to be the MIN (note that you need a top level in your list for this).

Here's the full blueprint:

Nathan Rudman, Anaplan Model Builder
4 REPLIES 4
Community Boss
Hi,

I'm having a hard time picturing your data.
"I have 2 dimensions, one is Dates and one is accounts" do you mean that you have the time dimension ? Are you talking about a transaction module ? Some pictures maybe ?

Usually, you should be able to use the Summary type min to get the lowest date and then you can pick it up from a line item that only has the Account dimension.

ps: If your additonal dimension is time you will need to activate "All periods" in the time settings and add [SELECT:Time.All Periods] to your formula.

Nathan Rudman, Anaplan Model Builder
Contributor

I think are right on the summary. but I'm not sure how to do it for dates.

and to visual my data.

basically in the test date column i want 2013-12-31 as it has an amount, not 2013-11-22 which is the first date.  and I want every value in the test date column to be 2013-12-31

Community Boss

Here's what I did:

The line items from Year Number to Day Number are an example of how to convert the text of your transaction to a date. In the real world, I would advise not to do it in this module but in a List Management XXX: Transactions module or even ideally have it loaded as a date format when you create the list. Anyway I put it here in case you need it.

The logic you're interested in here is the date line item. its formula is as below and its summary type is MIN

``IF amount <> 0 THEN Real Date format ELSE DATE(2099, 12, 31)``

Anaplan will consider an empty date to be the lowest, so you have to put a very high date so that it will be ignored.

Now the final line item is simply:

``Date[SELECT: 'forum: Date list min date'.All Transactions]``

It take the value of the Date line item at the summary level, which is set to be the MIN (note that you need a top level in your list for this).

Here's the full blueprint:

Nathan Rudman, Anaplan Model Builder
Contributor

thanks @nathan_rudman  that works perfectly