6 Replies Latest reply on Dec 14, 2018 1:28 PM by Matt George

    Average Top Months by Category


      Hey Guys! I am running into a problem with the level of detail that one of my calculations is happening on. The data that I am working on is not public, so what I have done is roughly recreated the scenario in the Superstore data set. This is what the setup looks like:

      Screen Shot 2018-12-07 at 8.52.36 AM.png

      Essentially, what I am trying to do is get the top 6 months of sales, and then be able to aggregate based on those months without months in the view. I also need this to be fixed on each sub-category. So, I need the top 6 months of sales for each sub-category. Then I need to be able to find the average sales of those 6 months, again fixed on sub-category. Up to this point I have tried to create a set with a limit of the top 6 months of sales. However, when keeping all of the sub-categories on the view it calculates this set based on top 6 months of all sub-categories, where I want to limit the LOD to the individual sub-categories. LOD's in the limit calculation haven't seemed to work, and try as I might with table calculations I haven't been able to come up with a solution. I have also considered ranking in SQL, but unfortunately the data I am working on comes from google sheets, and I am also restricted on making changes to the sheet itself. Any help you guys and girls might be able to give me would be greatly appreciated! I also don't know how clear this is, so if you need more clarification on the issue just let me know! Thanks so much!