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

    Equating the weeks numbers and plotting the results

    Sidhu Suresh



      I have my data like this.


      June 21, 2015June 21, 2015Cancelled92
      In Process50
      June 28, 2015Cancelled149
      July 5, 2015Cancelled149


      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.




        • 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