4 Replies Latest reply on Jun 8, 2016 5:14 AM by spencer.aronstein

    Identify Records That Have Corresponding Records In The Same Data Source

    spencer.aronstein

      I'm trying to come up with a way to compare results from a monthly self assessment(performed by each group of the business) against the results from the compliance assessment that is randomly performed on a small subset of groups each month. The goal is to see if the groups are accurate on their self-assessments compared to the compliance assessments. Some sample data is below, structured similarly to the actual data, and is also attached in Excel.

       

      What I'd like to be able to do is to compare the average of the compliance assessment scores from each month against the average of only the self assessment scores that also had a compliance assessment. For example, in January, I'd like to be able to know in Tableau that the average score on the compliance assessments was 80.67, and the average score on the self assessments for Furniture, Dining and Garden was 53.33. I've tried working with LOD calculations and also a workaround with index(), but haven't been successful so far. Any help you could provide would be most appreciated!

       

       

      FurnitureSelfJan-1688
      BeddingSelfJan-1692
      AccessoriesSelfJan-1685
      KitchenSelfJan-1674
      DiningSelfJan-1651
      Living RoomSelfJan-1684
      GardenSelfJan-1621
      FurnitureComplianceJan-1659
      DiningComplianceJan-1688
      GardenComplianceJan-1695
      FurnitureSelfFeb-16100
      BeddingSelfFeb-1695
      AccessoriesSelfFeb-1696
      KitchenSelfFeb-1693
      DiningSelfFeb-1691
      Living RoomSelfFeb-1645
      GardenSelfFeb-1688
      AccessoriesComplianceFeb-16100
      KitchenComplianceFeb-16100
      FurnitureComplianceFeb-1680
      DiningComplianceFeb-1678
      FurnitureSelfMar-1690
      BeddingSelfMar-1690
      AccessoriesSelfMar-1648
      KitchenSelfMar-1691
      DiningSelfMar-1695
      Living RoomSelfMar-1692
      GardenSelfMar-1693
      GardenComplianceMar-1696
      Living RoomComplianceMar-1685