Return lowest date on set of data

alismith

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?

 

Any advice

nathan_rudman

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: