Hi All,

I am trying to implement a functionality where in an end-user can view a dashboard based on 'quarter', 'month'. In this dashboard, i am facing difficulty to implement 'quarter' functionality for a metric - backlog. I have to show the value for backlog parameter on last day of every month i.e. 30, 31, 28 depending on month. I am able to implement this functionality for 'month' successfully. Please refer following:

Backlog Parameter (Calculated field - which i have to display on dashboard) has

case [Period Type]

when 'quarter' then if datetrunc('quarter',[Reference Date]) = datetrunc('quarter',[caldate]) and [cf_type] = 'Defect' and [backlog]>0 and (datepart('day',[caldate]) == datepart('day',[BacklogDate - CurrentTerm]) ) then [backlog] end

when 'month' then if datetrunc('month',[Reference Date]) = datetrunc('month',[caldate]) and [cf_type] = 'Defect' and [backlog]>0 and (datepart('day',[caldate]) == datepart('day',[BacklogDate - CurrentTerm]) ) then [backlog] end

end

Where:

- [Period Type] is parameter and contains 'month' , 'quarter' values.

- [Reference Date] is a parameter and contains data values from 1/1/2014 to 7/1/2014.

- [caldate] is a field in database

- [BacklogDate - CurrentTerm] is a calculated field which contains following

----------------------------------------------------------------------------------------------------------------------------------

if DATEPART('month',[Reference Date]) = 1 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 2 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 3 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 4 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 5 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 6 then Dateadd('month',1,[Reference Date]) - 1

elseif DATEPART('month',[Reference Date]) = 7 then Dateadd('month',1,[Reference Date]) - 1

End

------------------------------------------------------------------------------------------------------------------------------------

Issue:

Whenever I select [Period Type] as 'quarter', the logic does not work properly because:

- say an end user has selected 2/1/2014 as reference date and 'quarter' as period type; value on 28th of every month (Q1 - Jan, Feb, mar) gets summed up. Instead I want to display sum of value on last day of every month i.e. 31, 28, 31 (Jan, Feb, Mar).

I'd really appreciate if someone could please help me to implement the 'quarter' functionality.

Jim Wahl Jonathan Drummey Matt Lutton

Thanks,

Rohit Garg