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
Hi @Kanishq17
There are two ways to solve this. - none of them are pretty.
- First approach is to convert Periods into Days in Native Timescale. Owing to the implication on size, I would not want to prescribe this approach.
- Second approach is to use the mapping to derive the # of days in a period against the months of Native Timescale. This involves a 4 layer if formula (which can be optimised further). Below is the approach with screenshots (blueprints attached).
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.