Calculation on Months to Quarters

Highlighted
Occasional Contributor

Calculation on Months to Quarters

Hi ,

 

I have 2 lists. Months(1,2,3,45-...etc) and Qtr(1,2,3,4...etc). We need to create module to map months to Qtrs. That is for 3 months in one Qtr.

That is for month values 1,2,3 The Qtr value should be 1, For month values 4,5,6, the Qtr value should be 2 and so on.  

Please let me know if there is detailed method how to do so. 

 

Regards,

Rajasekhar

20 REPLIES 20
Highlighted
Super Contributor

Re: Calculation on Months to Quarters

Hi @Rajasekhar -

 

I think you have 2 General List (month and quarter).

 

You can do the mapping easily using system module.

 

Create a Module

System Module -> Month List as Dimension and 1 line item (List format - Quarter)

 

 

 

Capture.JPGCapture1.JPG

 

 

and then do mapping manually.

 

Thanks,

Sorna Raja Prabhu
Highlighted
Occasional Contributor

Re: Calculation on Months to Quarters

Thank you for reply, 

 

I tried  this earlier but was wondering if there is any other formula we can write than manually mapping it. 

 

I have two many months abd Qtrs to do it manually. 

 Please can you suggest 

 

Highlighted
Master Anaplanner/Community Boss

Re: Calculation on Months to Quarters

Hi Rajasekhar,

 

In your months list, create a new property called quarter and format it by the Quarter list

Then write this formula in the property:

IF MonthNum = 1 OR MonthNum = 2 OR MonthNum = 3 THEN FINDITEM(Quarters, "1") ELSE IF MonthNum = 4 OR MonthNum = 5 OR MonthNum = 6 THEN FINDITEM(Quarters, "2") ELSE IF MonthNum = 7 OR MonthNum = 8 OR MonthNum = 9 THEN FINDITEM(Quarters, "3") ELSE FINDITEM(Quarters, "4")

 

I am assuming you have a number formatted property in your month list called MonthNum. If not create this with the MONTH() formula

Also, the FINDITEM will only work if the name/code of the quarter list is 1,2,3,4 else replace the numbers in the formula with the name of the quarters

 

Best practice suggests not to keep any properties in the list. So do this in a System module after you get it working and delete the property form the list

 

Regards,

Anirudh

Highlighted
Occasional Contributor

Re: Calculation on Months to Quarters

Thank you Anirudh. I shall try and get back to you. Is therr any direct way apart from creating a property in the list
Highlighted
Master Anaplanner/Community Boss

Re: Calculation on Months to Quarters

@anirudh 

 

Please break this formula up to be:

LIne item 1: if Monthnum <= 3 then "1" else if MonthNum <= 6 then "2" else if MonthNum <= 9 then "3" else "4"

Line item 2: finditem(Quarters,'line item 1')

 

or a better way is to have a list of 1 through 12.  For 1->3 , you specify a 1, 4->6, you specify a 2, etc...then you just do a lookup on it.

 

Rob

 

Highlighted
Occasional Contributor

Re: Calculation on Months to Quarters

Hello Anirudh, 

 

My month list has only numbers 1,2,3 etc.. So how do i write my formula for monthnum number formatted property you were talking about. 

 

As far as i know month() is used when we want to conver time or date to number.. But this is already in number, so please suggest what to do. 

Highlighted
Master Anaplanner/Community Boss

Re: Calculation on Months to Quarters

Hi Rajasekhar,

That's perfect! Just substitute the MonthNum in my formula with your existing line item

I'm assuming your existing month number property is just called Month
So, use this formula instead:
IF Month = 1 OR Month = 2 OR Month = 3 THEN FINDITEM(Quarters, "1") ELSE IF Month = 4 OR Month = 5 OR Month = 6 THEN FINDITEM(Quarters, "2") ELSE IF Month = 7 OR Month = 8 OR Month = 9 THEN FINDITEM(Quarters, "3") ELSE FINDITEM(Quarters, "4")

After you complete this, do take notice of Rob's suggestion and use 2 line items instead. That smooths things for the calculation engine to run better

Regards,
Anirudh
Highlighted
Occasional Contributor

Re: Calculation on Months to Quarters

Hi Anirudh/Rob,

 

Thank you for your suggestions.

 

I have tried both of your approaches But seems I have made some mistake, as I'm not getting any Output. PFB the screenshots.

I have a number formatted property called Monthnum in my month list. 

I have a module with dimension as Month list on the rows and line items on the columns and I created 3 line items following both the Approaches with formula as below:

 

Qtrs Line item--Qtr List formatted(First approach)=

IF Months.Monthnum <= 3 THEN FINDITEM(Qtrs, "1") ELSE IF Months.Monthnum <= 6 THEN FINDITEM(Qtrs, "2") ELSE IF Months.Monthnum <= 9 THEN FINDITEM(Qtrs, "3") ELSE FINDITEM(Qtrs, "4")

 

Second Approach:

Qtrs Line Item1(Text formatted)=

IF Months.Monthnum <= 3 THEN "1" ELSE IF Months.Monthnum <= 6 THEN "2" ELSE IF Months.Monthnum <= 9 THEN "3" ELSE "4"

Qtrs Line Item 2(Qtr list formatted)= 

FINDITEM(Qtrs, 'Otrs Line item 1')

 

But I don't get any Output.

Community.PNG

 

Please can you help on the same.

 

Regards,

Rajasekhar

 

Highlighted
Frequent Contributor

Re: Calculation on Months to Quarters

Hi @Rajasekhar 

 

do you have code on Qtrs List?, Because finditem will search item Qtrs list based on code

 

Regards,

 

Panji