6 Replies Latest reply on Sep 14, 2016 9:50 AM by Tom W

    Reporting Period Calculation

    Sambit Kumar Mishra

      Hi,

       

      Need your help to generate a report which would be driven based on the selection of Reporting Period.

      Below is the sample Data :

      I want to create a dashboard to report the data as below :

       

      Below is the definition / formula for the metrics :

       

      Thanks in Advance!

       

      Best,

      Sambit

        • 1. Re: Reporting Period Calculation
          Abdulmonhem Alkhalil

          Hello  Kumar Mishra :

           

          One of the options i can think about it is to design 2 diff views and in each view you can sit diff date filter so the user will have the option to play with the date range , then in the second view hide the header for your field "Record Type" .

          finally combine both views in one dashboard and align both of them in proper way and  add the both date filter for each one .

           

          regards.

          • 2. Re: Reporting Period Calculation
            Tom W

            What do you need help with exactly, I don't see a question?

             

            You could create a calculated field called reporting period and then filter on that. I.e.

            IF [Date] >= '02/01/2016' and [Date] <= '05/26/2016' then '2/1/2016 - 5/26/2016'

            elseif [date] >= '5/27/2016' and [date] <= '9/27/2016' then '5/27/2016 - 9/27/2016'

            elseif insert other periods here

             

            end

            • 3. Re: Reporting Period Calculation
              Sambit Kumar Mishra

              Hi Tom,

               

              Thanks for your response!

               

              I want to create a reporting range period variable and use it as a dimension filter. Based on the Reporting Range Period selection, The metric should be calculated as below.

               

              Metric Business Definition:

               

              Newly Enrolled Patient = Count(CMID) WHERE initial Enrollment Date BETWEEN <Start Date> AND <END Date> of Reporting Period and Record Type = "Patient"

              Active Patient = Count(CMID) WHERE Date Entry BETWEEN <Start Date> AND <END Date> of Reporting Period and Record Type = "Patient".

               

              If you look at the definition of both the above metrics, One is dependent on initial Enrollment Date and other is depend on Date Entry and based on the selection of "Reporting Range Period", both the metrics should behaves correctly. The sample data and Report snap shot provided above.

               

              I understand, I can create different views (i.e one for Patient and other for Prescriber) and may be merge in one dashboard. But,My question is how to create calculation for Newly Enrolled Patient and Active patients in one view, So that I can replicate same for Newly Enrolled and Active prescriber in different view.

               

              Appreciate your help!

               

              Best,

              Sambit

              • 4. Re: Reporting Period Calculation
                Tom W

                Create the reporting range calculated field like I suggested above, that should be your starting point. Once it's created you can use it as a filter.

                 

                I don't understand your newly laid out metric business definitions - which reporting period are you specifically talking about?

                 

                Finally, you need to upload a Tableau Packaged Workbook including sample data. You should also create the calculated field for the reporting range as I outlined it above and include that in the sample.

                1 of 1 people found this helpful
                • 5. Re: Reporting Period Calculation
                  Sambit Kumar Mishra

                  Hi Tom,

                   

                  Thanks for your suggestion and I have created a Reporting Period as you have suggested. This is currently working for me. Below is the definition :

                   

                   

                  However, I am not sure, Why the calculated field value not given me expected result every time I switch between Data Source Tab and Worksheet. I observer The Calculated field value reset to Null every time.

                  But the interesting thing is, If I took a copy of calculated field, It's again working as expected. Please see below both the filter value.

                   

                  I am not sure why this is happening. Do you have any direction?

                   

                  Thanks for your help!

                   

                  Best,

                  Sambit

                  • 6. Re: Reporting Period Calculation
                    Tom W

                    the null will appear if you have dates which aren't covered by your IF statement.

                    So anything outside of the date range you've specified would be null.

                    I would suggest you change your IF to cover all possible date scenarios and put an else on the end to cover scenarios where the date isn't covered

                     

                    IF [Date] >= '02/01/2016' and [Date] <= '05/26/2016' then '2/1/2016 - 5/26/2016'

                    elseif [date] >= '5/27/2016' and [date] <= '9/27/2016' then '5/27/2016 - 9/27/2016'

                    elseif insert other periods here

                    else 'No Period Defined'