1 Reply Latest reply on Apr 1, 2016 3:15 AM by Simon Runc

    Equating the weeks numbers and plotting the results

    Sidhu Suresh

      Hi,

       

      I have my data like this.

          

      WEEK(Start_Date)WEEK(End_Date)StatusCNTD(ID)
      June 21, 2015June 21, 2015Cancelled92
      Completed135
      In Process50
      Rejected65
      June 28, 2015Cancelled149
      Completed126
      Rejected70
      July 5, 2015Cancelled149
      Completed105

        

      I want to create a calculated field like

      IF WEEK(Start_Date)=WEEK(End_Date) AND Status='Completed' THEN COUNTD(ID) END which equates the Start Date and End Date and filters only for Completed.

      Also my requirement is to plot these values as line chart against the WEEK(Start_Date). Please help me out in this.

       

      Regards,

      Sidhu

        • 1. Re: Equating the weeks numbers and plotting the results
          Simon Runc

          hi Sidhu,

           

          So one way to do this is to nest your logic statement (at row level) within an IF statement, and only return the ID if true (else NULL) and then COUNTD this (NULLs don't get counted in COUNTD)

           

          so would be something like

           

          COUNTD(IF DATETRUNC('week', [Start_Date])=DATETRUNC('week',[End_Date]) AND Status='Completed' THEN [ID] END)

           

          I've assumed here your dates are by day, so need to be TRUNC'ed to Week to compare

           

          You can then drag your start date into your Rows/Shelf and likewise with the new calculated field to get your plot

           

          Hope that does the trick, but please post back if not.

          1 of 1 people found this helpful