Two dimensions and Bi-Monthly Formula

HarishBabu

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.

@ManjunathKN @Misbah

rob_marshall

@HarishBabu

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

HarishBabu

@rob_marshall

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

Dikshant

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.

HarishBabu

@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.

Dikshant

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.

HarishBabu

@Dikshant Excel sample, Blueprint view, Regular view

Sai_Bharadwaj_Venati

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

HarishBabu

@Sai_Bharadwaj_Venati Thanks for your help.

I resolved the issue by creating 0 - 1000 C1 and 0 - 1000 C2, In C2 by referring C1 i created the forumula then combine them by adding two line items. Now I can be able to populate in that Dimension method.