# Calculation on Months to Quarters

Options

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

Tagged:

## Answers

• Options

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)

and then do mapping manually.

Thanks,

• Options

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

• Options

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

• Options
Thank you Anirudh. I shall try and get back to you. Is therr any direct way apart from creating a property in the list
• Options

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

• Options

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.

• Options
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
• Options

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.

Please can you help on the same.

Regards,

Rajasekhar

• Options

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

Regards,

Panji

• Options

Yes I have, Yet I'm unable to get any Output.

• Options

Hi  @Rajasekhar,

can you give me screenshot module months and blue print module months, because i need to know what dimension and line item you used.

• Options

Why is Time being used as a dimension in your module? 1,2 and 3rd month will always be Q1 or 1 in your case, irrespective of Time.

You can remove Native Time from your dimensions and it should work

Let us know if it doesn't work

• Options
Hi Rajasekhar,

Can you show me what the name and code of the quarter list looks like. I think the code is not 1,2,3 etc and that's why the finditem is failing

Regards,
Anirudh
• Options

Hi Panji/Misbah/Anirudh,

PFB all the required 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.

Regards,

Rajasekhar

• Options

The reason you are not getting anything on line items when FY19 is selected, is because of summary settings. The formulas should work fine.

Can you make the summary setting as FIRSTNONBLANK and check?

• Options

Yes, like @anikdas  say

the formula should fine, but if you see in FY19 the result will not  show  because the problem is your summary setting in none, so you need to check your summary setting and change none to formula.

• Options

Hi Rajasekhar,

You have applied the month dimension of Anaplan Time to your module. I don't think you would need it as you have already applied the Months dimension

To remove it, click Month under Time Scale of the Qtrs Line Item and change it to Not Applicable, repeat for all the Month dropdowns

Now when you look at any of the numbered months, you will be able to see the quarter number

Regards,

Anirudh

• Options

You are using native Anaplan Time dimension in the page selector which is selected to FY19 but your module is dimensioned to month, since default summary for line items is none, you wont see any value at FY19 which is a time summary , in case you choose any month like Jan 19 or feb 19 in the dropdown, you will be able to see values, hoever I think you do not need time dimension in this case, so you can set time in blueprint view to not applicable

• Options

Hi Anikdas/Panji/Anirudh,

Thank you for your guidance. I made the Timescale as Not Applicable and it worked.

Thank you so much for all the help.

Regards,

Rajasekhar

• Options

Thank you Kanish,

I have made the Timescale as Not Applicable and it works now.

Thank you for your help.

Regards,

Rajsekhar