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

Answers

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

  • 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

  • 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 

     

  • Thank you Anirudh. I shall try and get back to you. Is therr any direct way apart from creating a property in the list
  • @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

     

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

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

     

  • Hi @Rajasekhar 

     

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

     

    Regards,

     

    Panji

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

     

     

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

     

     

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

  • 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
  • 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.PNGCommunity 2.PNGCommunity 3.PNGCommunity 4.PNG

    Regards,

    Rajasekhar

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

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

     

     

  • 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

    anirudh_0-1592565775141.png

    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

     

  • 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

  • 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

  • Thank you Kanish,

     

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

     

    Thank you for your help.

     

    Regards,

    Rajsekhar