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
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,
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
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
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 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
Hi @Rajasekhar
do you have code on Qtrs List?, Because finditem will search item Qtrs list based on code
Regards,
Panji