3 Replies Latest reply on Nov 22, 2016 4:59 AM by Rashmi Sudhakar

# Show the data for the last day of the quarter

Hi,

I need some help in pulling out the data for the last date in the quarter, or if we have not yet reached the quarter we need to pull the data as of the current max date.

For Ex:

if we have the data as below:

 to_date count 1/31/2016 5,555 2/29/2016 5,491 3/31/2016 5,563 4/30/2016 5,483 5/31/2016 5,457 6/30/2016 5,516 7/31/2016 5,545 8/31/2016 5,645 9/30/2016 5,648 10/31/2016 5,675 11/5/2016 5,683

we need to get the data only for the quarter end, as in get the number for Q1 - march(3/31/2016),Q2 - june(6/30/2016), Q3 - september (9/30/2016), as we have not reached the end of Q4 we need  to pull the data for november(as per the above example).

• ###### 1. Re: Show the data for the last day of the quarter

Hi Rashmi,

Create the following calculated field and add it as a filter to your visualisation, keeping only TRUE values.

[to_date] = {fixed DATEPART('year',[to_date]),DATEPART('quarter',[to_date]):MAX([to_date])}

Hope that helps,

Ryan.

• ###### 2. Re: Show the data for the last day of the quarter

One other idea on how to do this without having to utilize a LOD calculation (they can introduce a little more complexity and can be costly if used when not needed) would be to create a calculated field to give a True/False distinction as Ryan suggested.

You can utilize this on the Filter shelf and keep only the "Last Day of Quarter" option.

IF [to_date] =

DATETRUNC('quarter',[to_date])

)

)

THEN 'Last Day of Quarter'

ELSE 'Other'

END

1 of 1 people found this helpful
• ###### 3. Re: Show the data for the last day of the quarter

Thanks Ryan!

But when i try to drill up the quarter level it shows the summation of the records of each month.