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

Answers

  • 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,

     

    SA

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

     

  • 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,

     

    SA