Hi Community,
There is a use case we were provided,
so data was loaded against 2 dimensions with a custom period list
also in a separate module the start and end date of each period is provided
the start date of period 1 and end date of period 13 is aligned with the time calendar FY20(1-Jan to 31 Dec)
the task is to get the values for the 2 dimensions against standard timescale (months) based on the periods to date mapping,
also the values in each period are assumed to be distributed equally for duration of the period, eg for f and India value for Jan 20 will be 1000(entire P1 is in Jan)+(2000/duration of P2)*no.of days P2 in Jan
Solved! Go to Solution.
Hi @Kanishq17
There are two ways to solve this. - none of them are pretty.
First create a module to do the Period to Native Month Mapping to identify the # of days falling in each month
Second, create an intermediate module to transform data from Periods to Native Months. You need to use both dimensions together here.
Lastly, Create the output module to calculate the results
P.S. In the period mapping that you posted, P12 and P13 has overlapping days. Although the formula caters to such occurrences, you may want to check that.
@Kanishq17 Let me know if this works for the problem statement. If it does please mark this as a solution so that others can refer to this.
Hi @anikdas ,
Oh, I didnt check that start date and end date of periods were overlapping, anyway thanks a lot for the solution, just a few doubts.
- Why have you used MAX function here ?
Anyways I tried and it is working good. Thanks a lot
@Kanishq17 There was one part of the if statement, which will calculate negative days if there is no overlap between period dates and month dates. If memory serves correct, it is the last part of the if statement. I thought Max is probably the easiest solution to that.