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