change date interval formula to display Day, Week no, or month name. 1

[javedi]

Hi

I'm trying to change the formula below to display the format of the date depending on option returned by the paramater, which is used in a crosstab.

The formula breaks the period down to day, week or month.

Any idea's on how to change this?

select {?Interval}
case "Day" : {agentconnectiondetail.startdatetime} // would like to display Day of week "Monday" rather than 1/03/2011
case "Week" : {agentconnectiondetail.startdatetime}-dayofweek({agentconnectiondetail.startdatetime})+1 // would like to display in No of week no "8" rather than 1/03/2011
case "Month" : {agentconnectiondetail.startdatetime}-day({agentconnectiondetail.startdatetime})+1 //would like to Display month "March" rather than 1/03/2011

Thank you in advance for any feedback.

Javedi

 

[lbass]

Leave the formula as is, and in preview mode, select the column heading->right click->format field->display string->x+2 and enter:

select {?Interval}
case "Day" : weekdayname(dayofweek(currentfieldvalue))
case "Week" : "Week "+totext(datepart("ww",date(year(currentfieldvalue),1,1,currentfieldvalue)+1,"00")
case "Month" : monthname(month(currentfieldvalue))

-LB

 

[javedi]

Hi lbass,

The last "case" is highlighted and an error "The ) is missing.) is returned.

I placed your formula under preview mode -> Format -> Common tab -> Display string -> x-2.

Not sure if this is any help but the formula i posted is also inserted as the column heading, in the crosstab. Is that the heading where this formula is placed?

Thanks,
Javedi

 

[lbass]

select {?Interval}
case "Day" : weekdayname(dayofweek(currentfieldvalue))
case "Week" : "Week "+totext(datepart("ww",date(year(currentfieldvalue),1,1),currentfieldvalue)+1,"00")
case "Month" : monthname(month(currentfieldvalue))

I assumed you were using your interval formula as the crosstab column--which you should continue to do so that the result is in the correct order. Then I meant for you to select the column label in preview mode->format field->display string->x+2 and enter the formula there.

-LB

 

[javedi]

Hi

In CR 11.5, I am using the interval formula as crosstab column.

This time the third currentfieldvalue is highlighted with an error stating "A number is required here" - 1,1),currentfieldvalue)+1,"00")

Javedi

 

[lbass]

Sorry.

select {?Interval}
case "Day" : weekdayname(dayofweek(currentfieldvalue))
case "Week" : "Week "+totext(datediff("ww",date(year(currentfieldvalue),1,1),currentfieldvalue)+1,"00")
case "Month" : monthname(month(currentfieldvalue))

-LB

 

[javedi]

Hi lbass

Many thanks for this. It's worked super fine.

Javedi