Descending number sequence with Dates (Months) as the heading

Assuming my heading is the time: Jan 19, Feb 19 to Dec. 20. This is due to a parameter set. and the range changes based on the parameter to be set.

My line item will be Date in number. this will make Jan 20 - Dec 20 to 1, 2, 3 and so on. I have successfully sequenced Jan 20 - Dec 20.

However Jan 19 to Dec 19 will have to be sequenced as -12, -11, -10 and so one. Making Dec 19 the -1 in the sequence.

How can i possibly pull this off using a number format?

Thanks,

Elaine

Accepted answers

anirudh

Absolutely!

Here's my time selection module and formulas:

The Year difference formula is very important:

ROUND((START(Launch Year) - START(Start Year)) / 365) * 12

And here's the formula for the Number line item in a module dimensioned only by time

IF ITEM(Time) < Time selection.Launch Year THEN IF ITEM(Time) = Time selection.Start Year THEN Time selection.Year difference * -1 ELSE PREVIOUS(Number) + 1 ELSE IF ITEM(Time) = Time selection.Launch Year THEN 1 ELSE PREVIOUS(Number) + 1

Result:

Let me know if that works

Regards,

Anirudh

All comments

anirudh

Hi Elaine,

Assuming your line item is called Number, try this formula in the line item

IF ITEM(Time) = Time.'Jan 19' THEN -12 ELSE PREVIOUS(Number) + 1

Let me know if it works

elaine.novel

It looks good except that u hardcoded "-12" in the formula. Is there a way to not hard code it?

Assuming the parameters state that:

Start year: FY18

Launch year: FY20

That is where the users set the parameters. So the start month of Start year and Launch year is January. From that given info, how can i make the sequence without hardcording the distance between Jan 18 (being the -24) and Jan 20 (being the +1). No "0" in between the +1 and the -1 by the way.

Thanks again,

Elaine

anirudh

Absolutely!

Here's my time selection module and formulas:

The Year difference formula is very important:

ROUND((START(Launch Year) - START(Start Year)) / 365) * 12

And here's the formula for the Number line item in a module dimensioned only by time

IF ITEM(Time) < Time selection.Launch Year THEN IF ITEM(Time) = Time selection.Start Year THEN Time selection.Year difference * -1 ELSE PREVIOUS(Number) + 1 ELSE IF ITEM(Time) = Time selection.Launch Year THEN 1 ELSE PREVIOUS(Number) + 1

Result:

Let me know if that works

Regards,

Anirudh

rob_marshall

@elaine.novel

Here is solution that hopefully works for you, I broke it up into 3 line items:

Elaine: IF ITEM(Time) = IF ITEM(Time) = SYS Global.Elaine Date THEN 1 ELSE IF NEXT(Elaine) = 1 THEN -1 ELSE IF ITEM(Time) < SYS Global.Elaine Date THEN NEXT(Elaine) - 1 ELSE 0

Next Elaine: IF ITEM(Time) > SYS Global.Elaine Date THEN PREVIOUS(Elaine) + 1 ELSE 0

Final Elaine: Elaine + Next Elaine

Hope this helps,

Rob

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385