1 Reply Latest reply on Jul 17, 2018 2:26 PM by Deepak Rai

    Averaging After Counting - LOD?

    Kyle Arvila

      Hello all,

       

      I have a bit of a conundrum that I was hoping to solve with Level of Detail expressions, but I'm really having a hard time figuring out where to anchor/set the level (or knowing whether it's actually possible). Hopefully one of you can at least point me in the right direction - thank you in advance!!!!

       

      So, what I am setting out to do is

      1. Display the total number of times (COUNT) each phrase appears and trend them over time (at the 'quarter' level)

      and

      2. Also display (or at least know/calculate/have available) the AVERAGE COUNT for each phrase, at the quarter level, across the entire data set.

       

      The problem I am running into is that, in order to get the answer to 1 (I already have), I have to COUNT (Category), which aggregates it. I am having trouble averaging this correctly at the quarter level to end up with the average number of times each phrase appears in any given quarter. I've included a (large, sorry - couldn't figure out how to attach a file) table with sample data.

       

      The result of the averaging I want to do should be 5.25 for Jelly and 4.75 for Peanut Butter, and then I should be able to display the times that each one occurs per quarter. The actual data set is more complicated; I would like to compare the categories after being filtered by my coworkers to the enterprise-wide data (#2 from above).

       

         

      TypeDate
      Jelly1/15/2016
      Peanut Butter1/16/2016
      Jelly1/17/2016
      Peanut Butter1/18/2016
      Jelly1/19/2016
      Peanut Butter1/20/2016
      Jelly1/21/2016
      Jelly1/22/2016
      Peanut Butter1/23/2016
      Jelly4/15/2016
      Peanut Butter4/16/2016
      Jelly4/17/2016
      Peanut Butter4/18/2016
      Jelly4/19/2016
      Jelly4/20/2016
      Jelly4/21/2016
      Jelly4/22/2016
      Peanut Butter4/23/2016
      Jelly4/24/2016
      Jelly7/15/2016
      Peanut Butter7/16/2016
      Peanut Butter7/17/2016
      Peanut Butter7/18/2016
      Peanut Butter7/19/2016
      Peanut Butter7/20/2016
      Peanut Butter7/21/2016
      Jelly7/22/2016
      Peanut Butter7/23/2016
      Peanut Butter7/24/2016
      Jelly10/15/2016
      Peanut Butter10/16/2016
      Peanut Butter10/17/2016
      Peanut Butter10/18/2016
      Jelly10/19/2016
      Jelly10/20/2016
      Jelly10/21/2016
      Peanut Butter10/22/2016
      Jelly10/23/2016
      Peanut Butter10/24/2016
      Jelly1/15/2017
      Jelly1/16/2017
      Jelly1/17/2017
      Jelly1/18/2017
      Peanut Butter1/19/2017
      Jelly1/20/2017
      Jelly1/21/2017
      Jelly1/22/2017
      Jelly1/23/2017
      Jelly1/24/2017
      Jelly4/15/2017
      Peanut Butter4/16/2017
      Peanut Butter4/17/2017
      Jelly4/18/2017
      Jelly4/19/2017
      Jelly4/20/2017
      Peanut Butter4/21/2017
      Peanut Butter4/22/2017
      Peanut Butter4/23/2017
      Peanut Butter4/24/2017
      Jelly7/15/2017
      Jelly7/16/2017
      Jelly7/17/2017
      Peanut Butter7/18/2017
      Jelly7/19/2017
      Peanut Butter7/20/2017
      Peanut Butter7/21/2017
      Peanut Butter7/22/2017
      Jelly7/23/2017
      Peanut Butter7/24/2017
      Peanut Butter10/15/2017
      Peanut Butter10/16/2017
      Jelly10/17/2017
      Peanut Butter10/18/2017
      Jelly10/19/2017
      Jelly10/20/2017
      Jelly10/21/2017
      Peanut Butter10/22/2017
      Jelly10/23/2017
      Peanut Butter10/24/2017

       

      Any information/insight would be extremely helpful! Thank you!