6 Replies Latest reply on Jun 21, 2018 12:56 PM by Deepak Rai

# Displaying last four Quarters

Following formula works because both Parameter 2  and QE Date are Date fields:

IF DATEDIFF('quarter',[QE Date],[Parameter 2]) >=0 AND

DATEDIFF('quarter',[QE Date],[Parameter 2]) <5

Then 'Last 4Q' END

But now I have a different scenario. Both Parameter 2 and QE Date are string data type.

How do I achieve the same thing ?

• ###### 1. Re: Displaying last four Quarters

You Can first Convert String To Date  To get CORRECT Results.  Pl Post Some Data if you need help in conversion.

Thanks

Deepak

• ###### 2. Re: Displaying last four Quarters

Both data look like this: I can use QE Date as a Date field. But I should not convert Parameter 2 to date as it is used in other places. So, only way is to convert to date in a calculated field. How do you convert string to date like the above format.

Basically, user will choose one date from the parameter, and the report should only show data for the last five quarters.

• ###### 3. Re: Displaying last four Quarters

I Think You can Follow this. It was Similar to Yours.

Just Split Your Column into Year and Quarter Using Custom Split in Data view or

SPLIT(Your String Date," ")

and Then Follow this; Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: Displaying last four Quarters

1. Quarter = SPLIT([Quarter End]," ",2)

2. Year = LEFT([Quarter End],4)

3. Quarter Number =

(IF     INT(RIGHT([1. Quarter]),1)=1 Then 1

ELSEIF INT(RIGHT([1. Quarter]),1)=2 Then 4

ELSEIF INT(RIGHT([1. Quarter]),1)=3 Then 7

ELSE 10

END)

3rd formula is giving error : RIGHT is being called with (string), did you mean (string, float) ?

• ###### 5. Re: Displaying last four Quarters

This one works for step 3:

IF INT(RIGHT([1. Quarter],1))=1 Then 1

ELSEIF

INT(RIGHT([1. Quarter],1))=2  THEN 4

ELSEIF

INT(RIGHT([1. Quarter],1))=3  THEN 7

ELSE 10

END

• ###### 6. Re: Displaying last four Quarters

Then You may Please Close Thread by Marking The Reply CORRECT.

Thanks

Deepak