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