3 Replies Latest reply on Dec 3, 2019 6:47 AM by Anthony Proia

    Totally Stumped - Please Help - Unique Aggregation of Monthly Data

    Anthony Proia

      Hi All,

      I spent a bit of time trying to figure this one out, but unfortunately I could not come up with a solution as i do not believe i have the expertise to solve this one.


      The Problem:


      Currently, I am looking to calculate the % yearly total of Offices [(Shrt Nm)] not submitting files ([0 Files Check]) for the cluster they are associated with [(Geo Cluster)]. If an Office has not submitted a file for a specific month it is flagged as 1, while if it has, it is flagged as 0.


      The Catch:


      My data is structured in a way that does not seem to be cooperating with tableau on this matter. For each Office, we have a flag to determine whether or not documents were filed per month. So for example, for a complete year, Office A will have 12 months associated with it and for each month there will be a 1 or 0 tagged with it to specify if files were filed or not. Whenever I attempt to do represent this data on a yearly level, it does not work out....I am looking to have each unique office be assigned a 1 or 0 to determine if it had files submitted that year or not, and than use that total to calculate the total % that did not submit. I tried using LoD calculations, but that did not seem to work for me.


      Tableau Example of issue:



      The expected results should be 3 and 100 if we look want to look at the yearly picture....but Tableau is aggregating each monthly line based on the formulas i used.


      I have attached a sample workbook and data set to demonstrate the issue I am having, the expected results would be as follows:



      Expected Results
      Main Cluster201733%
      Main Cluster201833%
      Main Cluster2019100%


      *edited expected results


      Thanks for any insight!


      Kind regards,