Calculation on Months to Quarters

[Rajasekhar]

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]

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,

[Rajasekhar]

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 

[anirudh]

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]

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]

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

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]

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]

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]

Hi @Rajasekhar 

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

Regards,

Panji