Two dimensions and Bi-Monthly Formula

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

Accepted answers

All comments

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

Aggregate numbers into cycles
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.

Quick Links

Can you help?

Unanswered questions

Hi Team , I am unable to login to the Anaplan portal using my credential.
Can someone help me i am unable to login anaplan portal using my credentials
Finding History for List Item Creation and changes
Dear Hive Mind, Do we have a way to get the history of a particular List Item, from its creation into a model up until its removal ? I am trying to chase when / how an item has been added into a model and how / when it would have been deleted and then recreated with the same code. Is there a pattern (manual or API) to get…
Action fails via cloudworks but works manually
I have an action in a process which is getting failed since 2 days when run via CloudWorks with below error . No issues when run manually . Same error occurs when run manually but as a warning "Import complete but with failures" This action is running since almost a year without any issue . No change has been made to…