Minimum item in a list grouping

Lets say I have a list to map any day in the year to an accounting month, because our accounting months don't always begin or end on calendar month boundaries.

Accounting Period Date List (sample)
              Parent     Code     Accounting Month
...           ...        ...      ...
5/24/2018                         May 18
5/25/2018                         May 18
5/26/2018                         Jun 18
5/27/2018                         Jun 18
...           ...        ...      ...
6/29/2018                         Jun 18
6/30/2018                         Jun 18
7/1/2018                          Jul 18
7/2/2018                          Jul 18
...           ...        ...      ...

Given an accounting month, I want to find the start date of that accounting month. In SQL this would be pretty straightforward:
SELECT MIN( TO_DATE( 'Accounting Period Date List'.ITEM, 'M/D/YYYY' ) )
FROM 'Accounting Period Date List'
WHERE 'Accounting Month' = Parameter:'Target Accounting Month Parameter'

What is the right syntax in Anaplan? I tried using the builtin Minimum for my Summary function, but I couldn't come up with anything where the syntax was valid for the equivalent of the SELECT and WHERE clauses.

Thanks,

Johnny

Accepted answers

All comments

Surya.Arif

Hi Johnny,

If you make a list of "Accounting Period Date List" like your example, would not it be tedious if you have to mapped

all 365 days into which period, not to mention the risk of error would be quiet high.

I would suggest for you to make an "Accounting Period List" and "Accounting Calendar Module"

Accounting Period List will be consist of your Accounting Period

- All Period

- Jan 18

- Feb 18

- ....

And Accounting Calendar Module

Will have "Accounting Period List" Only as Dimension

and 2 line item

- Period Starting Date -> Type Date

- Period Ending Date -> Type Date

It would be like this

Accounting Period Starting Date Ending Date

Jan 18 01-01-2018 25-01-2018

Feb 18 26-01-2018 25-02-2018

Mar 18 26-02-2018 25-03-2018

So when we want to take the starting date or ending date, we can use lookup.

Regards,

JHumphrey

Thanks, Surya.

1) Loading the list happens once per year, and there are only 11 period boundaries, so it isn't much more tedious or error-prone than listing the start and end dates of the periods. It is easy to do in a spreadsheet.

2) The main point of the list is to go from a date (or a string that looks like one) to a period, not the other way around, and that's why the list was structured as it was.

3) Because the main use is to go from any date to the corresponding period, with the start and end date approach we would need a way to lookup when a date is BETWEEN some dates. Is there a lookup for that?

4) Even if we went with an alternate data structure, I'm still really curious about how to translate what appears to be straightforward (aggregated) SQL into Anaplan. Does anybody have a reference for that?

Surya.Arif

I am also a SQL Server Programmer, so I know how hard it is to translate things into Anaplan.

But after I get used to Anaplan, I learned that, I should stop to comparing Anaplan with SQL Server.

It will be better if you try to compare it with excel it will be easier that way.

The way Anaplan do aggregate function will be more like how you do VLOOKUP in Excel.

It is not just a simple command SUM, but you have to have a comparison cell (Line items in Anaplan)

then a reference table (in excel) or module/list (in Anaplan). then you can get the aggregate that you want.

Back to your case.

I will try to solve your case by using accounting date to select the corresponding Accounting period

1. We need to make the list that you need, let call it "Accounting Period"

And then add another property "Period" with the data type of "Time period - Month"

Fill some data and it would be like this

Parent Code Period

2018-01-01 Jan 18

2018-02-01 Feb 18

.......

!! Important Notes

in SQL Server they can automatically convert when dealing with Dates.

But here we are using List and the data type is TEXT

So when we enter "2018-01-01" , "01/01/2018" , "01-Jan-2018", they all treated differently, be careful with this

make sure u use the same date format

2. We make a sample module call it "Accounting Period Sample",

In my sample I only have Version as the dimension

and 2 line item

- "Accounting Date" with data type "List - Accounting Period"

- "Accounting Period" with data type "Time period - Month"

3. In the line Item "Accounting Period" - FORMULA, enter the following formula

"Accounting Period.Period[LOOKUP: Accounting Date]"

Explanation how to understand the formula

Accounting Period : This is the List or Module from where we want to do the aggregate in this case Look up

Period : This is the property (if list) or line item (if module) that we wanted to the operation to get the result

LOOKUP : This is the aggregate, like LOOKUP, SELECT, SUM ....

Accounting Date : This is the line item that define what data that we have to search for

4. Next every time you Choose the "Accounting Date" it will automatically look up the corresponding Accounting Period.

Several important point to remember here.

1. When you do want to look up from a list like "Accounting Period", you must make sure that you use the "Accounting Period" Item. I mention this because from my experience, I tried to lookup from a "Text" line item

that I fill with the data I want (in your example will be the accounting date), It can not.

You have to use the same list key or module key, to get their corresponding item.

2. The result corresponding item that we want ("Accounting Period") both have to be the same type. The one in the "Accounting Period" property and the target Line item must be the same, in this case I use "Time period - Month"

That should solve your problem.

I just trying to give an explanation based on your description.

If there is still any problem, a screenshot of your sample module would make it easier to understand your problem

Regards,

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…