About andre.lie

@Sivasankar    Based on my suggested approach earlier, we can use TIMESUM to identify missing range from the fourth module (which is by supplier, maetrial, range, and time) .   You said that missing price range would be placed in the same timescale as the previous range.  As in your earlier attachment, there is quantity of 26,000 (Range 3) in Week 34 FY20, and of 37,000 (Range 5) in Week 35 FY20.  My question is which week the missing price range 4 should belong to.  Based on your explanation, I understand that it should be in Week 34 FY20 i.e. same timescale as previous range.  However, we cannot put it in Week 34 unless we have another list as part of the module.   As you said there are 16 price ranges, this is the number of list items that will be in the range list. They can be re-used for each supplier and material combination even though the range value themselves are different for each combination (price module uses supplier and material list).  However, if adding a price range list will blow the size of the module (I am not sure how big your supplier and material lists are), another option is to use a running number list item instead of actual range to reduce number of items in that list.  The fourth module will be similar to this.       I have not really tried it but think that it can be achieved despite of now requiring a more complex transformation.  The number of items in that list can be probably reduced depending on how many missing price ranges can really happen between two adjacent weeks   If there is still problem with the module size, we can probably: - Make a final module as a one list (numbered list) module with line items only (no timescale) - With base contract module as source, use action to create the numbered list (supplier, material, and range as code) and load final module - Create another action to insert the missing ranges.  I do not have an exact idea though.   Regards, Andre ... View more

@Sivasankar ,   What I think you can do is - In your quantity module, determine the price range for that quantity by formula.  I guess you have done this.  How many price range do you have?  - Build the third module (second module is the existing price range module) which is by supplier, material, and range (no timescale).  This module is used to determine which range is missing over all periods from the base quantity module  - Build the fourth module which is by supplier, material, range, and week timescale.  This will be the final module.  It refers to base quantity module, price range module, and the third module.   As your quantity module uses Timescale, which week do you plan to put the missing price range?   Based on the steps above, I will have something below as the final module.  Here assumption is made that missing range will be placed in the same week as the valid one.  For example, in Week 31 there is quantity for Range 2, so missing Range 1 is placed in Week 31 as well.  In Week 35 there is quantity for Range 5, so missing Range 4 is in Week 35.  In Week 34 there is quantity for Range 3 and there has been quantity for Range 2 in Week 31, so nothing is inserted in Week 34     Regards, Andre   ... View more

@CommunityMember117833 , I agree with what @einas.ibrahim has suggested ... View more

@CommunityMember117833 ,   Screenshot of your module will be helpful.  You can refer to the month in your time dimension using ITEM(TIME)   Regards Andre ... View more

@anaroytcs    Is there any criteria if document approval is required, for example line item value achieving a threshold in the module? Or it happens as and when another user who is using Anaplan decides that an approval is required.   If this is not to be an automated notification, you might want to consider creating a line item with MAILTO function which will open up a compose email when the line item's value (which is a link) is clicked by the user.  We can pre-populate the recipient address, subject and body of the email by linking to other line items.   ... View more

Congratulations @SerenaZ for the fantastic work! ... View more

I agree with this idea.    If removal cannot be done automatically, it will be great if there is an additional button to remove the items from the mapping manually instead of marking them as ignored.  Our only option at the moment is to rebuild the action. ... View more

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

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

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

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

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

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