1 Reply Latest reply on May 25, 2016 6:31 AM by John T

    Calculation Question: Aggregate / Non-Aggregate (Joining tables with dates, but reporting by group)

    John T

      Hi all, I'm continuing with building a few reports and have run into another issue that I haven't encountered yet, while working with Tableau. Since the data is sensitive, I've attached some sample data that is similar to the data that I'm working with.

       

      The only difference is in the sample data, both tables are in Excel while in my situation, I'm working with one static table in Excel and one table from a SQL database. (I think this may change some of the calculations? As I'm unable to join the tables since they're from different sources.)

       

      So, my first table has a business date, a group, and a return number:

       

      In terms of group number, I'd like to bucket them further, with Groups A and D being labeled as "Tech Items" and Groups B and C being labeled as "Healthcare Items"

       

      In my second table, I have another table that lists each business date as well as an average return number:

       

      For my Tableau report, I'm trying to create a data table that will list out the sum of the actual returns for each category. Actual returns, in this situation, is defined as: [Return] - [Average Expected Return].

       

      So basically, for the "Tech Items" category, I would like it to calculate the sum of actual returns for every group (A and D) within this category.

       

      Thank you so much for helping, this community has been so helpful / kind and it's really helped me so much. I know this is a bit messy, so please let me know if you have any questions