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:

 

 

nathan_rudman

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.

alismith

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

alismith

thanks @nathan_rudman  that works perfectly