9 Replies Latest reply on Aug 15, 2014 12:08 PM by Rohit Garg

    Month vs Quarter view of dashboard

    Rohit Garg

      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