2 Replies Latest reply on Sep 19, 2018 5:14 AM by Kevin Buckman

    Quarterly average with blending

    Kevin Buckman

      First off, I'm not able to post a workbook as the data to build this view is confidential, but I'll try and give a good enough rundown of the structure of it and where I'm stuck. Working with monthly data, everything is as expected, but going quarterly I cannot get to what I need.

       

      My primary data source is a file that contains FTE information for each branch by month. Each branch is within an area, then a region, and a division. It is blended with a case incident file which can contain 0 to many records per branch per month. I've blended because my goal is to figure out the total FTE per level and the total cases per level. Left joins caused duplication in the FTE data as there could be several cases in a given AU. My viz is the Period and Division on rows and SUM(FTE) and SUM(Cases) for columns. All works great looking at months.

       

      When I move to try and summarize data quarterly, at the moment I'm trying to get the average FTE per division per month within a quarter, eventually also calculate cases per FTE within the quarter. We don't want to count the FTE's 3 times as this is a quarterly calculation hence my need for averages. Due to blending, I cannot divide by COUNTD(Period) or anything similar that I have tried to mimic. Hardcoding to 3 is fine, but I feel like that isn't the best way to solve my issue if for some reason we go annually or something like that. I'd have to manually update all of the calculations. I think there should be a way to make this function. I've tried table calcs and I just get weird numbers that I cannot figure out where they came from. If I take the AVG(FTE), it just gives me the average per branch in each region tallied up to the division. Which isn't right either.

       

      If necessary, I can try to make a scrubbed version to post, but just wanted to present the problem first and see if it's a common problem that I haven't managed to find the solution for. Is it not easily solvable with Tableau like some posts I read indicated?

       

      Thanks for any help that is provided!

        • 1. Re: Quarterly average with blending
          Patrick A Van Der Hyde

          Hello Kevin,

           

          If it is possible to sort the data on the FTE values such that you can run index() for each FTE, then you could reset at each FTE and use the value to divide the total.  If you have any sample data, it would help a lot.

           

          I might also suggest a LOD calculation with the FTE value as the level of detail being used.  

           

          Patrick 

          • 2. Re: Quarterly average with blending
            Kevin Buckman

            I don't believe LOD calculations on blended data are permitted. I haven't had much luck with getting window calcs to function either and that is one of the other recommendations I've read. I'll take a look at the index method and see what is possible.

             

            If I have some time this week, I'll see if I can't put together something that doesn't have the identifying information but still mimics the setup.