3 Replies Latest reply on Jan 1, 2017 9:36 PM by Sireesha J

    Two date fields and one measure

    Sri K

      Hi Friends,

       

      I have 2 date fields(Project start date and Project Finish date) and one measure is count of Projects. I want to display how many projects are started and finished on 2015, 2016 and 2017(quarter wise).

       

      I have taken two date fields on single axis and showing the values, i have shown lines and getting correct values for all years. When I am trying to filter in Project start date and project finish date as 2015, 2016 and 2017 year values I am getting wrong values.

       

      Here I am attaching the twbx file for reference, please suggest me how can I show, how many projects are started and how many projects are finished in the 2015 Q1, 2015 Q2, 2015 Q3------ 2017 Q3, 2017 Q4.

       

      And suggest me different visualization other than line chart.

       

      More appreciate for helping on this.

       

      Thanks in Advance.

       

      Thanks,

      Sri

        • 1. Re: Two date fields and one measure
          Sireesha J

          Hi Sri!

           

          Try below method, it will give you desired result and more visualisation options.

           

          1) Using pivoting option to pivot the two date fields as shown below. It will give you an option to filter the dates easily.

          Capture1.PNG

          Capture.PNG

           

          Regards,

          Sireesha.

          • 2. Re: Two date fields and one measure
            Sri K

            Hi Sireesha,

             

            Thanks for your reply.

             

            I didn't get what is pivoting there. I have 2 date fields and one measure. For better understanding please look attached twbx file on original post.

             

            Thanks,

            Sri

            • 3. Re: Two date fields and one measure
              Sireesha J

              Hi Sri!

               

              I have done pivoting of the two date fileds in your data set to get a combined date field as shown below. The reason for this, you may not have the same dates in both the Project Start Date and Project Finish Date and in order to display the number of projects start and end in a particular quarter, you need to have common date field.

               

              If you want avoid this step, you need to modify the data accordingly as shown below. To get number of projects, just take COUNTD(Project Code).

               

              Attached reference workbook of 10.1 Version

              Hope it gives you better understanding.

               

              Regards,

              Sireesha.

              1 of 1 people found this helpful