1 Reply Latest reply on Jul 8, 2016 8:42 AM by Salvatore Pepe

    How To Eliminate Duplicates At Some Levels of Hierarchy, But Keep In Others

    Salvatore Pepe

      Hello Everyone,

       

      I have a hierarchy of dimensions that I am looking to have a sum, or running total in the case of percentages, across different drill-down levels of the hierarchy. I've included an example of what I'm running into. Say you have a therapeutic area of drug treatments (cardiovascular), drugs within that therapeutic area (Drugs A and B), the disease states that those drug are used to treat ( Disease 1, 2, 3), the trials that were used to support that indication (BLOOD, HEART and VEIN), and a count of the number of male/female patients in those trials. Notice that the heart trial was used to support the indication of Disease 1 and Disease 2. They way I currently have the hierarchy set up is Therapeutic Area -->Drug --> Disease --> Trial. When I drill down on the blue hierarchy pill, the software is summing all values to the level of the most specific member of the hierarchy. However, when summarizing by therapeutic area and drug, I would ideally like the calculation to ignore the fact that the HEART trial is duplicated and count it only once, since I don't want to double count these patients. But when summarizing by Disease and Trial, I would like the information to be summed the way Tableau is currently doing it. I know that I could create separate analyses for the different levels of the hierarchy, and probably a calculated field with logic to ignore/keep the values in each specific instance, but the ability to have one worksheet that drills down is desirable.

       

      Any help that could be provided would be greatly appreciated!


      Sal