4 Replies Latest reply on Jun 8, 2016 6:36 AM by Joseph Sutkowski

    How to Aggregate a Subset of Data Based on Dynamic Criteria

    Joseph Sutkowski

      Hi all,

       

      This question has plagued me for quite some time and I figured I'd give it a shot here!

       

      What I want to do:

       

      Build a pie chart that aggregates only the latest values per store based on whether or not their food was good recently! (Will make sense in the sample data I've provided below!)

       

      What I've tried:

       

      • I tried using a few LOD calculations EG include and fixed, but they got messy fast!
      • I additionally tried restructuring the data using custom SQL. While effective, this split my project into two data sources, which won't work because I need to apply multi-select quick filters across all of my views.

       

      Sample Data:

       

      *Max dates for each store are bolded* 

       

      StoreDateIs the Store Cheap?FoodIs the Food Good?Cost
      Acme6/10/2016YesBaconYes9
      Acme6/10/2016YesTurkeyNo10
      Acme6/11/2016YesHamYes11
      Acme6/11/2016YesSausageNo12
      Acme6/12/2016YesCheeseYes13
      Acme6/12/2016YesMilkYes14
      Stop and Shop6/6/2016YesToastYes15
      Stop and Shop6/6/2016YesButterYes16
      Stop and Shop6/7/2016YesBaconYes17
      Stop and Shop6/7/2016YesTurkeyYes18
      Stop and Shop6/8/2016NoHamYes19
      Stop and Shop6/8/2016NoSausageNo20
      Wal-Mart6/10/2016YesCheeseYes21
      Wal-Mart6/10/2016YesMilkNo22
      Wal-Mart6/11/2016YesToastYes23
      Wal-Mart6/11/2016YesButterNo24
      Whole Foods6/6/2016NoBaconNo25
      Whole Foods6/6/2016NoTurkeyNo26
      Whole Foods6/7/2016NoSausageNo28
      Whole Foods6/8/2016NoCheeseYes29
      Whole Foods6/8/2016NoMilkNo30
      Whole Foods6/9/2016NoToastYes31
      Whole Foods6/9/2016NoButterNo32
      Whole Foods6/10/2016NoBaconYes33
      Whole Foods6/10/2016NoTurkeyNo34
      Whole Foods6/11/2016NoHamYes35
      Whole Foods6/11/2016NoSausageYes36
      Whole Foods6/12/2016NoCheeseYes37
      Whole Foods6/12/2016NoMilkNo38

       

       

      Desired Result:

       

      Good FoodCost
      Yes106
      No82

       

      The desired result only sums up the cost for each store's latest date and then puts them into the appropriate category: either good or bad food.

       

       

       

      Let me know if you have any questions and thanks again!

       

      Best,

       

      Joe