3 Replies Latest reply on Sep 23, 2018 11:36 PM by Rahul Singh

    Calculations

    Praks Sha

      Hi All,

      I need your help to derive following calculations in Tableau.

       

      I am counting Performance indicators, which have different frequency of reporting time for example End of month, End of Financial Year, End of Financial quarter.

      I need to count the indicators based on their latest reporting time.

      Currently, if i count all the indicators in last 3 months, some indicators values are getting tripled, because their frequency of reporting time is every month end. I only want to count indicators based on latest reporting.

       

      Please help, how can i derive the dynamic start date and End date of Each indicator's reporting period

       

      Thanks.

        • 1. Re: Calculations
          Rahul Singh

          Hi,

           

          It would easy for us if you could replicate this problem using some sample dataset (e.g. superstore dataset) or give the expected output format in excel file.

           

          Regards,

          Rahul

          • 2. Re: Calculations
            Praks Sha

            Hi Rahul,

             

            Thanks again for looking into my problem. Superstore dataset would not help me to replicate this issue.

            I try again to explain my issue here.

             

            I have data as :

             

                

            Indicator KeyIndicator IDIndicatorNameFrequencyOfReporting
            110007346Crude rate of surgical site infections following a caesarean section procedure per 100 proceduresEnd of  month
            210007350Rate of central-line catheter associated blood stream infections  in intensive care unitsEnd of financial quarter
            310007351Rate of healthcare-associated Staphylococcus aureus bacteraemia infections per 10,000 bed daysEnd of financial quarter
            410007356Percentage of unplanned readmissions within 28 days following discharge after a birthing episodeEnd of month
            510007357Percentage of unplanned readmissions within 28 days after discharge following birthEnd of month
            610007358Average gain in the Functional Independence Measure score per day of care for rehabilitation patientsEnd of financial quarter

             

            Now, if i want to count all the reported indicators in last 3 months (July -Sep), i will get the data 3 times more for Indicator keys 1,4,5. Because every month these are being reported.

            So my issue is how to get the latest reported time only , so that for Indicator keys 1,4,5 i will get only Count which were last reported in Sep and ignore the old data.

            In other words i need to find the Indicator Reporting Start date and End date dynamically so that it will pick the latest reported indicator only.

            Hope it is more clear now.

             

            Thanks.

            • 3. Re: Calculations
              Rahul Singh

              Hi Praks,

               

              I don't know if my solution would work for your case as i don't have any idea how your raw data looks like. Still, i have tried to replicate the scenario using superstore data, with an objective to get latest month sales value in a particular state and quarter. For example, for Alabama, in 2014Q4, this will pick sales for December instead of November or October.

               

              Attaching file for reference. Hope this sparks some idea to solve your problem. Please close the thread if this helped you.

               

              Regards,

              Rahul