Month vs Quarter view of dashboard
Rohit Garg Aug 13, 2014 10:02 AMHi 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