Get latest date and price in Month dimension

weilun.wong
weilun.wong
in Modeling

Hi,
Can anyone guide me how to build and create line item formula to fulfil this requirement?
We have a flat data in excel, inside has date, product and price, the data can be entered and uploaded not in sequence, it means today i can upload for 6 Apr, tomorrow i can upload for 5 Apr.
I need a module then can shows the data by Month, indicate what is the latest date and price received for that product. Like the sample above, even though tomorrow i upload for 5 Apr, i still want to show 6 Apr because 6 Apr is the latest. Attached with a sample of input and expected output.

weilunwong_0-1649216244289.png

 

Tagged:

Best Answer

  • Amaya
    Amaya
    Answer ✓

    Hi @weilun.wong 

     

    This is similar Q&A

    https://community.anaplan.com/t5/Anaplan-Platform/Last-Occurence/m-p/60979#M11060 

     

    And in your case, since the output has Months as a dimension, the intermediate module must also have Months.

    To display the last day of the month, you would get the rank of the dates with the RANK function and then calculate the biggest RANK number of the month in the intermediate module for each product.

    8fd9ad71-5703-4340-aab8-50f42d774771.png

    Step2.png

    You return the result to the flat file module then you know which data to aggregate as you have painted it yellow.

    Step3.png

    The results can then be displayed by linking the output module with a MAX or SUM function.

    Step4.png

     

    Hope this helps,

    Taichi

Answers

  • Jitendra.Vishnolia
    Jitendra.Vishnolia

    Hi @weilun.wong , 

     

    This one is a interesting one.

    • What is the amount of data you are loading ? 
    • Do you really require the daily data in Anaplan which is not the latest one?

     

    The approach used by me is a totally unconventional approach for this scenario. Please find below the approach 

    Step 1:

    Create a Data Module with 

    • Time Scale as Day; Product List as dimension
    • Line items : uploaded Date and Price (Set Summary of Uploaded Date as Last non blank and turn off summary for Price line item)
    • Load the Data in this module  (with 2 columns of date; 1 to map with Time scale other for line item)

    JitendraVishnolia_1-1649222229411.png

     

    Step 2:

    Create another module with

    • Time Scale as Month; Product List as dimension
    • Line Items 
      • Updated Date : Refer the Last non blank line item in the previous module
      • Price : Refer the price with lookup of the updated date from current module

    JitendraVishnolia_2-1649222479465.png

     

    This way you will get the expected results.

     

    There might be other workarounds. Looking forward to more inputs from others regarding the conventional approach aligning to the best practices on this thread.

     

    ~ Jitendra

     

     

     

     

  • weilun.wong
    weilun.wong

    Hi Jitendra,

    It is due to user want to see the historical price in system too, that's why need to keep all the data.
    Thanks for your solution.

  • weilun.wong
    weilun.wong

    Hi Taichi,

    Your solution looks good to me, it save the space of having a day dimension module to store the input, thanks for your help.

Categories