Mean time interval between transactions

Highlighted
Frequent Contributor

Mean time interval between transactions

Hi All,

 

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

 

image.png

 

image.png

 

Thanks

Arun

Message 1 of 12
11 REPLIES 11
Community Boss

Re: Mean time interval between transactions

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. 

 

Capture.JPGCapture2.JPG

Hope it helps!

Manuela

 

Message 2 of 12
Frequent Contributor

Re: Mean time interval between transactions

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.

 

image.png

Message 3 of 12
Community Boss

Re: Mean time interval between transactions

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. 

Message 4 of 12
Contributor

Re: Mean time interval between transactions

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  

image.png

Hope it helps.

 

Thanks 

Message 5 of 12
Frequent Contributor

Re: Mean time interval between transactions

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

 

Thanks

Arun

Message 6 of 12
Contributor

Re: Mean time interval between transactions

@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  ??

Message 7 of 12
Super Contributor

Re: Mean time interval between transactions

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

 

So if it's possible to load in the previous posting date as mentioned by @Manuela_Apostol  and @JV271090 , 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.

 

Screen Shot 2019-06-12 at 1.47.26 PM.png .   Screen Shot 2019-06-12 at 1.57.52 PM.png

'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)

 

Screen Shot 2019-06-12 at 1.48.05 PM.png

'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.

Screen Shot 2019-06-12 at 1.48.18 PM.png

Screen Shot 2019-06-12 at 1.57.11 PM.png

Screen Shot 2019-06-12 at 1.48.28 PM.png

 

Thanks,

LipChean

Message 8 of 12
Frequent Contributor

Re: Mean time interval between transactions

Thank you @LipChean_Soh  for a such detailed explanation.

 

Regards

Arun

Message 9 of 12
Super Contributor

Re: Mean time interval between transactions

You're welcome @ArunManickam 

Message 10 of 12