# Two dimensions and Bi-Monthly Formula

Options
edited April 2 in Modeling

RESOLVED:
I am trying to write a Formula for Bi-monthly value which also needs to consider in monthly level into 4 parts
0 - 1000, 1000 - 2000, 2000 - 3000 and 3000 and above

Here, I create a custom timeline for Bi-monthly calculation.
for example: Jan (1- 15) has 2500 and Jan ( 16 -31 ) has 3100. Hence total Jan has 5600.

i have created a two-dimension module Please refer image attached below.

Tagged:

• Options

Why the custom time dimension, why not just have 3 dimensions: Time, c1 and C2, and Intervals?

• Options

I need to display the hierarchy as follows:

Cycle 1 (1-15)

Cycle 2 (16-30)

Separately, for Cycle 1 and Cycle 2.

Please share your opinion what can we proceed if we have 3 dimensions for that logical

• Options

Can you show how the custom time dimension looks like and if it's a parent child so enabling the summary should work? Can you explain more on how the modules are structures and where exactly you wanted to show 5600 for Jan Month, I guess in front of Interval? Please share the blueprint mode screenshot as well.

• Options

@Dikshant
Here the challenging part is defining the remining data in 2000- 3000 bucket, only 500 is filled in Cycle 1. So cycle 2 needs to consider the remining 500 which is not filled in Cycle 1.

C1 Jan 23 is Bi-month which parent of 15 days, I can get the summarized value. I am trying to distributed the values as per above mentioned bucket like 0-1000, 1000 - 2000, 2000-3000 and 3000 above. Here cycle 1 fills upto 2500 means, There are 500 data not filled in Cycle 1. now the Cycle 2 starts from 2000-3000, but only 500 needs to consider here.

• edited April 1
Options

Thanks, this helps. Can you please create a mockup in Excel as well like how you wanted the calculations and numbers to be redistributed? So that we have the scenarios for which we are trying to solve the problem.

• Options

@Dikshant Excel sample, Blueprint view, Regular view

• Options

Hi

1. Aggregate numbers into cycles
2. Split Cycle 1 and Cycle 2 data based on Min/Max buckets
 Cycle1 6000
 Cycle2 5000

Cycle+Cycle2 11000

Min

Max

Cycle 1 Output

Cycle 2 Output

0-1000

0

1000

1000

0

1000-2000

1000

2000

1000

0

2000-3000

2000

3000

1000

0

3000 above

3000

1000000

3000

5000

Cycle 1 output formula ⇒ If cycle 1 aggregate number greater than min band and max band then Band Max- Band Min else if Aggregate number greater than minimum but less than maximum then aggregate number - Min band number else 0

Cycle 2 output formula ⇒ If cycle 1 aggregate number greater than max band number then 0 else if Cycle 1+ Cycle 2 aggregate number greater than Min Band number and max band number then MAX Band - Min Band - Cycle 1 output else if Cycle 1 + Cycle 2 aggregate number ⇐ Min band number then 0 else Cycle 1 + Cycle 2 aggregate number - Cycle 1 output number - min band number

Excel formulas: -

Cycle 1 Output → =IF(AND(\$E\$4>=I4,\$E\$4>=J4),1000,IF(AND(\$E\$4>=I4,\$E\$4<=J4),\$E\$4-I4,0))

Cycle 2 Output → F(\$E\$4>=J4,0,IF(AND(\$E\$6>=I4,\$E\$6>=J4),1000-K4,IF(\$E\$6<=I4,0,\$E\$6-K4-I4)))

E column aggregate demand; Cycle 1 → E4; Cycle 2 → E5; Cycle 3 → E6

I,j Column → Min Max numbers

K,L Column → Cycle1, 2 output numbers