Get latest date and price in Month dimension

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.

idea_135347.png

Accepted answers

Amaya

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.

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

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

Hope this helps,

Taichi

All comments

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)

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

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

Amaya

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.

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

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

Hope this helps,

Taichi

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

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.

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…