Calculation on Months to Quarters

Rajasekhar · June 2020

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

prabhu · June 2020

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,

anirudh · June 2020

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

Rajasekhar · June 2020

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

Rajasekhar · June 2020

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

rob_marshall · June 2020

@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

Rajasekhar · June 2020

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.

anirudh · June 2020

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

Rajasekhar · June 2020

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

Panji · June 2020

Hi @Rajasekhar

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

Regards,

Panji

Rajasekhar · June 2020

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

Panji · June 2020

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.

Misbah · June 2020

@Rajasekhar

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

anirudh · June 2020

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

Rajasekhar · June 2020

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.

Comminty 1.PNG Community 2.PNG Community 3.PNG Community 4.PNG

Regards,

Rajasekhar

anikdas · June 2020

Hi @Rajasekhar

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?

Panji · June 2020

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.

anirudh · June 2020

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

Kanishq17 · June 2020

hi @Rajasekhar

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

Rajasekhar · June 2020

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

Rajasekhar · June 2020

Thank you Kanish,

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

Thank you for your help.

Regards,

Rajsekhar

Calculation on Months to Quarters

Answers

Categories