How to define no. of buckets from the days information and then dynamically lock those many buckets using a DP macro

[Former Member]

Hello Experts,

I am dealing with a complex macro, which firstly calculates the number of days in a key-figure(KF) "D-Time" and I have to convert those days into monthly buckets assuming that the monthly buckets have days same as calendar days i.e.. Feb bucket will have 28 days, Mar bucket will have 31 days and Apr bucket will have 30 days and so on.  

So if I run the macro today(which is 6th day of the current bucket) and calculated days in the "D-Time" KF comes to as 28 then we know that "D-Time" is 28 days FROM TODAY ie. it is crossing the current month-end and going in next month because

6(today)+28 Days("D-Time")= 34 total days [1 month+3 days(of second month)] so output required is "2" as the number of buckets

and these 2 buckets should be locked/freezed [cell_input (0)] for another KF named "Entry".

And for ex. this macro were to run on first day of the month then total days will be

1(today)+28Days("D-Time")= 29 total days i.e. still same month so we need to freeze only current month.

So according to above logic or requirement there are two main tasks.

1. calculating the total days(based on when macro is run) --> calculating this is comparatively easy but translating this information into buckets is what we want to achieve and then

2.locking/freezing only those many buckets in future for another KF named "Entry". This is tricky and hardest part of it because we need to be able to pass and translate the bucket information into number of iterations so as to freeze/lock those many buckets only.

Thanks

Harshad M.

Message was edited by: Harshad M

[Former Member]

One more point that it should happen at certain level of aggregation, i.e. Macro should check if

agg_level("product") =1 and agg_level("location")=1