Convert Month Name to number

[srinu_anu2007]

Hi,
i have month level which is having the members: jan 2014,Feb 2014...Dec 2014.below is the calc which is taking long time and can some one help to tune this query.

case
substring (caption ([Selected Month]),1,3)
WHEN ('Jan') Then (1)
WHEN ('Feb') Then (2)
WHEN ('Mar') Then (3)
WHEN ('Apr') Then (4)
WHEN ('May') Then (5)
WHEN ('Jun') Then (6)
WHEN ('Jul') Then (7)
WHEN ('Aug') Then (8)
WHEN ('Sep') Then (9)
WHEN ('Oct') Then (10)
WHEN ('Nov') Then (11)
ELSE (12)
END

and [Selected Month]= it's always current month-1 i.e Aug 2014 then output is:Jul 2014


#prompt('Param_Month','MUN','[rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].['
+
Case timestampMask($current_timestamp,'mm')
when '02' then 'Q1'
when '03' then 'Q1'
when '04' then 'Q2'
when '05' then 'Q2'
when '06' then 'Q2'
when '07' then 'Q3'
when '08' then 'Q3'
when '09' then 'Q3'
when '10' then 'Q4'
when '11' then 'Q4'
when '12' then 'Q4'
else 'Q1'
end
+
' '
+
Case timestampMask($current_timestamp,'mm')
when '01' then _add_years (timestampMask($current_timestamp,'yyyy'),-1)
else timestampMask($current_timestamp,'yyyy')
end
+
'^'
+
Case timestampMask($current_timestamp,'mm')
when '02' then 'Jan'
when '03' then 'Feb'
when '04' then 'Mar'
when '05' then 'Apr'
when '06' then 'May'
when '07' then 'Jun'
when '08' then 'Jul'
when '09' then 'Aug'
when '10' then 'Sep'
when '11' then 'Oct'
when '12' then 'Nov'
else 'Dec'
end
+
' '
+
Case timestampMask($current_timestamp,'mm')
when '01' then _add_years (timestampMask($current_timestamp,'yyyy'),-1)
else timestampMask($current_timestamp,'yyyy')
end
+
']')#

MUN: [rp_gcrs].[rp_period].[rp_period].[Month]->:[TM].[rp_period].[rp_period].[@MEMBER].[Q1 2013^Feb 2013]



Please help me

thanks,

[bdbits]

It appears you are using a dimensional data source. Is this for a slicer? Cube or DMR? If there is a database involved, which vendor? Do you have a time dimension that includes year, quarter, month, and day (time dims always should in my opinion)? Can the cube/database be changed to link to your time dimension instead of just storing a string? This alone could simplify things a lot and give you more flexibility in reporting without resorting to long expressions.

[srinu_anu2007]

Yes, I am using DMR and source as TM1 cube. I am using the [selected month] as data item and my hierarchy is having year,month and quarter levels and when i use the substring its killing my report.

Thanks,

[MFGF]

Yes, I am using DMR and source as TM1 cube.

Hi,

This seems like a contradictory statement. Are you using DMR or are you using a TM1 cube? DMR is Dimensionally Modelled Relational (ie the source is a relational database) and TM1 is a TM1 OLAP cube (ie the source is a TM1 cube resident in memory).

Why are you doing all this horrible stuff converting month name captions to numbers then building quarter groupings from the numbers then converting the month numbers back to month names? It doesn't seem to make any sense.

MF.

[srinu_anu2007]

iam using Tm1 OLap cube and the style is DMR. i.e. as per client requirement only am developing.

[srinu_anu2007]

Hi MF,

Thanks your response.

1. this my data item:  substring (caption([rp_gcrs].[rp_period].[rp_period].[Month]),1,3), actually it will give you the output is jan, feb,..dec

2. one more data item, it has converting string to numeric:
case
substring (caption([rp_gcrs].[rp_period].[rp_period].[Month]),1,3)
WHEN ('Jan') Then (1)
WHEN ('Feb') Then (2)
WHEN ('Mar') Then (3)
WHEN ('Apr') Then (4)
WHEN ('May') Then (5)
WHEN ('Jun') Then (6)
WHEN ('Jul') Then (7)
WHEN ('Aug') Then (8)
WHEN ('Sep') Then (9)
WHEN ('Oct') Then (10)
WHEN ('Nov') Then (11)
ELSE (12)
END

3. this is killing my reports and its taking long time and iam not sure how to reduce the performance.

any idea please help me.

Thanks,

[MFGF]

iam using Tm1 OLap cube and the style is DMR. i.e. as per client requirement only am developing.

Hi,

You're using a dimensional package over a TM1 cube. You are not using DMR - DMR is dimensionally modelled relational data. It is a dimensional package over a relational database.

Hi MF,

Thanks your response.

1. this my data item:  substring (caption([rp_gcrs].[rp_period].[rp_period].[Month]),1,3), actually it will give you the output is jan, feb,..dec

2. one more data item, it has converting string to numeric:
case
substring (caption([rp_gcrs].[rp_period].[rp_period].[Month]),1,3)
WHEN ('Jan') Then (1)
WHEN ('Feb') Then (2)
WHEN ('Mar') Then (3)
WHEN ('Apr') Then (4)
WHEN ('May') Then (5)
WHEN ('Jun') Then (6)
WHEN ('Jul') Then (7)
WHEN ('Aug') Then (8)
WHEN ('Sep') Then (9)
WHEN ('Oct') Then (10)
WHEN ('Nov') Then (11)
ELSE (12)
END

3. this is killing my reports and its taking long time and iam not sure how to reduce the performance.

any idea please help me.

Thanks,

Why are you performing these awful calculations? You have shown us the syntax you are using but you haven't described why you have built them or what it is you are trying to achieve? It looks to me like you are somehow trying to code your way around a deficiency in the design of the cube - perhaps calculating quarter groups because there are no quarter members defined in the cube? Can you tell us what it is you are trying to do here?

MF.

[srinu_anu2007]

this is the calculation which we r using as per req in our reports.

= (Year-to-Go Forecast/(12-n)-YTD Actuals/n )÷ (Year-to-Go Forecast/(12-n) *100
n = selected month


Difference between YTD Actuals average and Year-to-Go Year to Go Forecast average divided by Year-to-GoYTG Forecast *100

[MFGF]

this is the calculation which we r using as per req in our reports.

= (Year-to-Go Forecast/(12-n)-YTD Actuals/n )÷ (Year-to-Go Forecast/(12-n) *100
n = selected month


Difference between YTD Actuals average and Year-to-Go Year to Go Forecast average divided by Year-to-GoYTG Forecast *100

Hi,

You're thinking about this in relational terms, and coming up with a hugely complex expression as a result.

Your YTD Actuals is, in fact, the Actuals averaged across each month from the beginning of the year. You don't need to calculate the YTD total and divide by the number of months to get this. You simply use the Average aggregate function across the month members from the beginning of the year to the current month.

ie instead of [YTD Actuals] / <number of months> you would use

average([Actuals measure] within set <your set of YTD month members>)

The same concept applies for the Year-To-Go forecast - instead of [Year-to-go-forecast total] / 12-<number of months> you would use

average([Forecast measure] within set <the set of month members from next month to the end of the year>)

This should set you going in the right direction.

Cheers!

MF.

[srinu_anu2007]

Hi MF,
Thanks for your help and suggestion. but i have to do as per req and i will try ur suggestions also.

Thanks,
Venkat

[srinu_anu2007]

Hi,

can't we do as per my requirement?

Thanks,

[MFGF]

Hi MF,
Thanks for your help and suggestion. but i have to do as per req and i will try ur suggestions also.

Thanks,
Venkat

Isn't the requirement to get the correct result? Or does your client somehow dictate what method you should use in the report to do it? If so, why?

I have taken your relationally-based expression and given you a more efficient approach to figure out the same values dimensionally. It *is* per your requirement, surely? The result for each should be the same, so how is it not per your requirement? You asked for ways to make your calculation more efficient - the approach I'm suggesting should do exactly that.

MF.

[MFGF]

Taking this approach, another solution for you might be:

((total([Forecast measure] within set <the set of month members from next month to the end of the year>) / count([Forecast measure] within set <the set of month members from next month to the end of the year>)) - (total([Actuals measure] within set <your set of YTD month members>) / count([Actuals measure] within set <your set of YTD month members))) / (total([Forecast measure] within set <the set of month members from next month to the end of the year>) / count([Forecast measure] within set <the set of month members from next month to the end of the year>)) * 100

MF.