About andre.lie

andre.lie

It will be helpful to have information for any line items that are used or mapped in actions either in list or line items mapping tabs. This will prevent deleting them accidentally when performing a model cleanup

andre.lie

@jimfeng5 If you prefer having 445 Month Start and End as input, I do not think you need to enter First Week and Last Week. Second, Third, Fourth Week number line items are not necessary either. You can use: - PERIOD function for the First Week Period : PERIOD('445 Month Start'), - First Week Period + 1 / 2 / 3 for Second to Fourth Week Period, - this logic to check if it is a 5-week month for Last Week Period : IF (Period End - Period Start + 1) / 7 = 5 THEN '1st Week' + 4 ELSE BLANK

andre.lie

@jimfeng5 I think we need to have a month to week mapping as below Following on what @Jared Dolich has detailed in his article Now assuming we have this weekly sales, where Wk 31-34 is 700 each and Wk 35 sales is 900 The unadjusted monthly sales is 3700, less 5 days for Wk 31 adjustment, plus 1 day for Wk 35 adjustment Regards Andre

andre.lie · ‎02-26-2020

Hi @usman.zia , I definitely agree with your points. Regards, Andre

andre.lie · ‎02-26-2020

Hi @jonathanpascual If your line item is text, by default the Summary method will be set to None. You can show the text at Parent level by changing it to First non-blank or Last non-blank. Regards, Andre

andre.lie · ‎02-17-2020

Hi All, Similar to what all of you have mentioned, what we did was to load the file to a flat staging module and create a reconciliation module which is by time and line items only. In the staging module, we create line item that uses FINDITEM to identify the possibly unmapped list items for each list, and create module view with filter to show the unmapped items only. The reconciliation module summarizes data in the staging module and target module such as total value in source file (staging module), total value in target module, total value of unmapped items of each list. When administrator notices that there is unmapped value in the reconciliation module, further investigation is done via module views in the staging module. Regards, Andre

andre.lie · ‎02-12-2020

Currently, History page only allows us to filter based on date range. I think the search functionalities should be expanded further. It should be categorized at least as metadata (structural) and data changes. This will enable us to review model structural changes apart from comparing revision tags. For example, after creating an action in Development that performs insert into a production data numbered list, we run the action and do further structural changes. This will result in a lot of Add Item rows returned in History search result, which makes the history review of other structural changes made prior to creating the action difficult. The same thing happens if there are many other data changes in between structural changes. Furthermore, we can also limit the search by event. For example, for structural changes, we can do further search for Add Line Item, Delete Line Item, or any other structural event.

andre.lie · ‎02-10-2020

Hi @takushimozuru , What is your module's time scale? What is the most recent date definition? Is it last date that has data against any Place list item or is it something else? Regards, Andre

andre.lie · ‎02-02-2020

Hi @Jmanaplanner46 , Almost similar to @jnoone approach, if it is required for the date and month to be in 2 digits, I usually convert the date to number using formula YEAR(DATE FORMAT) * 10000 + MONTH(DATE FORMAT) * 100 + DAY(DATE FORMAT), and then convert it to text format using TEXT and MID functions. Regards, Andre

andre.lie · ‎12-05-2019

@rob_marshall, I did not mean to doubt your thought or experience that it is not a best practice to include transaction data as part of key in source system. I thought that it might be a feasible workaround in Anaplan if anything else could not be changed.

andre.lie · ‎12-04-2019

@rob_marshall I think for the source system to make yesterday vs today records unique, it will still need date and transactional data to form surrogate key. In that case, it will be the same as Anaplan creating a combined customer, product, date and transactional data.

andre.lie · ‎05-07-2019

Hi All, I have a model with Week General time settings. Module 1 uses Day timescale and Module 2 has Date formatted line item. How can I lookup line item in Module 1 using Date formatted line item in Module 2? The Day timescale in Module 2 appears as 10 Apr 2019 while Date line item in Module 2 appears as 4/10/2019. I think I need to convert 4/10/2019 to the format of Day timescale. However, I do not find that I can format a line item as Day timescale. Thanks, Andre

andre.lie · ‎01-08-2019

Hi, I have role A and B in the model. Both roles have Write access to module 1, which has line item 1-5. However, for line item 5, role A should have read access, while role B write access. How can I assign roles to line litems access? Thanks Andre

andre.lie · ‎01-08-2019

Hi, I have module '1' with a line item which is list 'A' formatted published to a dashboard. I also have list 'A' published as page selector, as it will drive module '2'. How can I synchronize my line item in module '1' with the page selector i.e. if list item 1 is selected on page selector, line item value in module '1' is defaulted to list item 1 as well Thanks Andre

andre.lie · ‎01-08-2019

@DavidSmith, @ben_speight, @rob_marshall Thanks for the suggestions. I think having an export module with the required line items is the most appropriate one for my situation. Andre

andre.lie · ‎12-20-2018

Hi @rob_marshall, Thanks for the response. I need the lists to be as columns for upload to another system. Regards, Andre

andre.lie · ‎12-20-2018

Hi Everyone, I have a view that I want to export into a file via action. The view has 2 lists as column and 3 line items across. The module itself has 7 line items. However, the export always output all 7 line items. Is there a way to limit the export to the selected line items only? Regards, Andre

andre.lie · ‎11-21-2018

Thanks @DavidSmith

andre.lie · ‎11-21-2018

Hi Adam, Thanks for the quick reply. It is not about exporting data from module. I would like to download import file that I have uploaded to Anaplan cloud. I can do this via Anaplan Connect, but would also like to be able to do it via dashboard. Regards, Andre

andre.lie · ‎11-21-2018

Hi, I understand we can use Anaplan Connect to download file that has been uploaded. Can we also do this via Action that is published to a dashboard? I do not find Action that can download a file. Regards, Andre

andre.lie · ‎10-29-2018

Hi, I have a module with a numbered list. Among the module line items are Submit and Approve boolean, which I use for some kind of worlflow. When I use Copy Branch action to copy a particular numbered list, both booleans are also copied. On the other hand, I just want to copy other line items but the Submit and Approve boolean. Is there any workaround to use Copy Branch action just for selected line items? Regards, Andre

andre.lie · ‎10-21-2018

Hi Darlyn, Yes, David's suggestion makes sense. Thanks Andre

andre.lie · ‎10-09-2018

Hi @DavidSmith, Thanks for the quick response. Sorry for not being clear about the question. Let's say I have a module of 3 lists: List1, List2, List3. The module line item is 'Value' Items in List 1: Item 11, Item 12 Items in List 2: Item 21, Item 22, Item 23 Items in List 3: Product 1, Product 2, ..., Product 100 I want to find the Top 10 products for each combination of items in List 1 and List 2 However, the RANK function only allows one list for the 5th parameter which is the Group parameter. If I use RANK(p1,p2,p3,p4,ITEM(List 1)), the rank will take into account all items in List 1 and List 3, creating rank from 1 to 300 (3 x 100), and vice versa if I use RANK(p1,p2,p3,p4,ITEM(List 2)), the rank created will be from 1 to 200 Regards Andre

andre.lie · ‎10-08-2018

Hi, I have a module with 3 or more lists, and Value and Rank line item. I want to rank Value for each combination of items in those 3 lists. However, I find that RANK function can only accommodate 1 list in the Group option. How can this be done? Regards, Andre

andre.lie · ‎10-01-2018

Hi Ernie, Thanks for the response. My initial intention was to test Anaplan Connect with database. So, I used Access as I thought it was the easier one to test on my local. As there was problem with Access, I decided to test it using MySQL. The connection worked as I expected but I included the JDBC connection information in the Anaplan batch file instead of using a separate JDBC properties file. It still did not work with separate properties file though. Here is the working set Operation in my batch file. set Operation=-file "Anaplan_Demo_Sql" -jdbcurl "jdbc:mysql://localhost:3306" -jdbcuser "username:password" -jdbcquery "select distinct name from world.city where name <> 'San Miguel'" -import "SQL from Anaplan_Demo_Sql" -execute -output "C:\Data\Anaplan\301 Anaplan Connect\anaplan-connect-1-3-3-5\error.txt" Regards Andre

andre.lie · ‎09-19-2018

Hi All, I am trying to use Anaplan Connect to import data from MS Access table to Anaplan list. The table has only one column containing list values. I have created Anaplan import action based on text file with similar structure i.e. one column file. I create jdbc.properties file as below jdbc.connect.url= "Jdbc:Odbc:Driver={Microsoft Access Driver(*.mdb); DBQ=C:\Data\Anaplan\301 Anaplan Connect\Database1.mdb};" jdbc.username= jdbc.password= jdbc.fetch.size=10 jdbc.isStoredProcedure=false jdbc.query=” SELECT * FROM Table1” Database1.mdb and Table1 are my access database and table name I use this Operation in Anaplan bat file, where I put the name of the import action after -file, and location of the jdbc.properties after -jdbcproperties. set Operation= -file "Prospects from Prospects.txt" -jdbcproperties "C:\Data\Anaplan\301 Anaplan Connect\anaplan-connect-1-3-3-5\jdbc.properties" I downloaded MS Access JDBC driver for MS Access and copy the jar file and other extracted folder to Anaplan Connect folder. However, when I run the bat file, it gives message that it cannot find lib directory Do I miss anything or set incorrect parameter in the .bat and jdbc.properties configuration? Regards, Andre

andre.lie · ‎09-04-2018

Hi Ethan, Thanks for the detailed response. The information certainly gives me a better understanding of the workflow. My apology for late reply. Regards, Andre

andre.lie · ‎06-28-2018

Hi All, What is the budget rejection mechanism in Anaplan? Is there a way to reject budget submitted by cost center user, reviewed by manager and rejected, which then notifies user via email for instance Regards, Andre

andre.lie · ‎06-27-2018

Thanks for the response. The country in the second module is populated correctly. It is list formatted as Country L3. The formula: PARENT(ITEM('Cost Center L4')). I think I will use 'Country L3'.Local Currency[LOOKUP: 'Cost Center L4'.Country] as you suggested earlier.

andre.lie · ‎06-27-2018

Hi, Thanks for your advice. It really works. I previously also tried with 2 staging line items in the second module: cost center and country, both are List types. Values are derived from the first module, where in the first module: cost center = 'Employee L5'.Cost Center[LOOKUP: employee] country = 'Employee L5'.Country[LOOKUP: employee] while 'Employee L5'.Cost Center = PARENT(ITEM('Employee L5')), and 'Employee L5'.Country = PARENT(PARENT(ITEM('Employee L5'))) Then for Currency, I tried: 'Country L3'.Local Currency[LOOKUP: Country] However, I got this error: The formula for .....Currency is invalid Is it because of the same reason of having PARENT function in the Lookup, even though it is indirect? Thanks Andre