Mean time interval between transactions

ArunManickam

Hi All,

 

How to calculate mean interval between transactions per material in the following module which is one dimensional. 

 

 

 

Thanks

Arun

LipChean_Soh

Hi,

 

I want to mention about 1 point in advance, i.e. RANK has a cell limit as mentioned in https://help.anaplan.com/anapedia/Content/Calculation_Functions/All/RANK.html?_ga=2.1143538.25158203.1560335469-1239638901.1550667215

 

So if it's possible to load in the previous posting date as mentioned by @Manuela_Apostol  and @Jitendra.Vishnolia , we should do so.

 

However i still want to discuss a possible solution that might be useful, if not with your particular issue, then maybe with some other issues.

 

 .   

'Transactions' is a list that rolls up to 'Materials'.

'Rank' is a list, with a property 'Previous Rank' of 'Rank' format. (Please create this in a SYSTEM modules as part of DISCO)

 

'Transaction Module' has the 'Posting Date', and the general idea is to rank the 'Transactions' within each 'Material'. Then the 'Rank' number is converted to 'Rank' list, so that we can then send it to another module 'Find Transaction'.

 

'Find Transaction' will place the relevant 'Transactions' sequentially for each 'Material'. And referring to Rank.Previous Rank, we can then find the Previous Transaction.

 

Back in 'Transaction Module', we can then populate 'Previous Transaction', and subsequently populate 'Previous Posting Date' and then calculate 'Days since Previous'.

 

Changing the summary method of 'Days since Previous' will give you the average of 'Days since Previous' at Materials level.

 

Thanks,

LipChean

Manuela_Apostol

Hi Arun, 

 

As a first step you need to calculate the difference between the Date of Today and Previous Posting Date. At the moment Anaplan does not have a formula integrated as Excel to retrieve Today's date (NOW()), so you need to import that everyday using Anaplan Connect or select it manually.

 

However, for the testing purposes, you can just create a module and manually select the date of today. Then as simple using (Today-PSTNG Date) you will get 'Days since Previous'.  On the same line item (in blue print), you can change the summary method to Average, and get the result you are looking for. You can chose to calculate the average as well in a separate line item (use Select: Total).

 

Please find below a simple example I created in Anaplan based on my explanation. As well, there are other ways of calculating this, also depending of what do you want to do further with the result. If is statistics related, I recommend you to check for Statistics best practices on Anapedia. 

 

Hope it helps!

Manuela

 

Jitendra.Vishnolia

Hi @ArunManickam ,

 

As per my understanding, You need to fetch the Previous Posting Date, then by subtracting the Previous Posting Date from Posting date you can get the Days since previous.

 

To fetch Previous Posting Date You can Either 

• Import directly from the source , or
• Sort the Posting Date Column in Ascending order, Create a line item of Previous Posting Date and paste all the Posting Date data by skipping the material wise first Transaction .

 

As per my understanding, please find the Snapshot of what I tried in Anaplan  

Hope it helps.

 

Thanks 

ArunManickam

Thanks for your inputs. We have 200k materials and millions of transaction. Copying and pasting is not feasible as a solution.

 

Thanks

Arun

ArunManickam

Thanks for this. This may potentially lead to a solution, but not yet.

 

We have today's date, so no worries.

 

But average with respect to today's date is not the same as mean interval between subsequent transactions.

 

Jitendra.Vishnolia

@ArunManickam : Can't you import Previous posting date Directly from Source (i.e. Seems to be Excel in your case) as you are importing the Posting Date in your module  ??

Manuela_Apostol

HI @ArunManickam , 

 

I understood, that was my understanding that you need the comparison with today's date. Sorry

 

if I read your answer, in fact the issue is with getting the Posting date in Anaplan?

 

The best is to make an import, then instead of using Today's date use the one imported. 

ArunManickam

Thank you @LipChean_Soh  for a such detailed explanation.

 

Regards

Arun

LipChean_Soh

You're welcome @ArunManickam 

karank_*4578

@LipChean_Soh 

 

This is brilliant!!!

 

Just a quick question is your rank list  a fixed list ? i.e fixed number of ranks ? 

LipChean_Soh

Hi @karank ,

 

Yes, Rank is a simple List with members from 1 to X.

 

X should be a number less than the highest number of 'Transactions' list found within any 'Materials' list, if we refer to my illustration above.

 

And it's always safer to put in a buffer for X, since it won't contribute that much Workspace to the module 'Find Transactions', i.e. # of Materials multiplied by # of Ranks.

 

Thanks,

LipChean