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