Custom Time Aggregation

LILYLIU
Frequent Contributor

Custom Time Aggregation

Dear Anaplan Experts,

 

Would it be possible to build a custom time aggregation function while still being able to leverage our system time functions?

 

For example, I am doing some salary calculations at the granularity level of day, then I would like to aggregate the period of 2022-05-25 2022-06-24 to June 2022 because our payroll calendar is not inline with the model calendar.  So when we say June's salary, we mean May 25 to June 24. 

 

Would it be possible?

 

Thanks,

 

Lily

2 ACCEPTED SOLUTIONS

Accepted Solutions
rob_marshall
Moderator

Re: Custom Time Aggregation

@LILYLIU 

 

To accomplish this, check out the following...

 

I have a SYS Time Month module which tells me the start and end dates for the calculation.

 

2021-05-26_12-31-30.png

 

In my data module (by day), I have the transactional detail, a line item telling me the month which will be used for a lookup, and the data to be summed.

 

2021-05-26_12-33-41.png

 

The formula for the data to sum is: 

 

IF START() >= sys month david.Start Date[LOOKUP: Month] AND START() <= sys month david.End Date[LOOKUP: Month] THEN Transactional ELSE 0

 

2021-05-26_12-33-59.png

The result is:

2021-05-26_12-31-30.png

 

Hope this helps,

 

Rob

View solution in original post

abhay.kanik
Regular Contributor

Re: Custom Time Aggregation

@LILYLIU 

 

You can create a list with fake time and map it with the days you have . You will need a module with time on day scale and a line item. which will be mapped to each day.

 

Fake time sys.PNG

I suppose you data is something like this -

fake time data.PNG

In the final module there should be both actual time and fake time , there sum the line item by fake time mapping you did in system module.

the final output will be like this, formula you can see in the image below - 

Fake month final.PNG

 

while native summary by time like like this-

normal time.PNG

 

Thanks

Abhay

View solution in original post

3 REPLIES 3
rob_marshall
Moderator

Re: Custom Time Aggregation

@LILYLIU 

 

To accomplish this, check out the following...

 

I have a SYS Time Month module which tells me the start and end dates for the calculation.

 

2021-05-26_12-31-30.png

 

In my data module (by day), I have the transactional detail, a line item telling me the month which will be used for a lookup, and the data to be summed.

 

2021-05-26_12-33-41.png

 

The formula for the data to sum is: 

 

IF START() >= sys month david.Start Date[LOOKUP: Month] AND START() <= sys month david.End Date[LOOKUP: Month] THEN Transactional ELSE 0

 

2021-05-26_12-33-59.png

The result is:

2021-05-26_12-31-30.png

 

Hope this helps,

 

Rob

View solution in original post

abhay.kanik
Regular Contributor

Re: Custom Time Aggregation

@LILYLIU 

 

You can create a list with fake time and map it with the days you have . You will need a module with time on day scale and a line item. which will be mapped to each day.

 

Fake time sys.PNG

I suppose you data is something like this -

fake time data.PNG

In the final module there should be both actual time and fake time , there sum the line item by fake time mapping you did in system module.

the final output will be like this, formula you can see in the image below - 

Fake month final.PNG

 

while native summary by time like like this-

normal time.PNG

 

Thanks

Abhay

View solution in original post

LILYLIU
Frequent Contributor

Re: Custom Time Aggregation

Thank you so much, both! It works perfectly.