1 Reply Latest reply on May 24, 2017 4:28 PM by Shinichiro Murakami

    Average of aggregate with LOD

    bhavik.contractor

      I am having a difficult time calculating some metrics which involves LOD and aggregate on top of the LOD calculations.

      To summarize, I have a hierarchy of nation > region > state. To keep it simple, I am just considering a hierarchy of Region > State in the attached example.

      I have to calculate a certain score metric at state level first and then aggregate the metric at region level where the score at region level = average (scores at state level).

      The challenge is that the data is joined using 3 different tables which are not at the same grain.

       

      Below is a detailed breakdown of the requirement

      • I have a fact table which is at daily level. Then I have a couple of other supplementary fact tables which are at month level, and are joined to the daily fact table on year nu, mon nu and the common dimension/s.

       

      1. Tables:
        1. dlyFactTable gives day level data of number of posts and responses, for a Region and the States within the Regions. Furthermore the measures are broken down by the media type. So there are
          • 4 dimensions: Date, Region, State, type, and
          • 2 facts: Responses, Posts
          1. There are 2 complimentary tables which is manually generated data and provided separately. It has data at month level
            • Received Messages - This table provides the number of messages received per State at month level, and broken down by media type
              1. Dimensions: State, type, Mon Nu, Year Nu
              2. Fact: msgs_rcvd_cnt
          2. Response Goal - This table provides the Response Goals at month level per State
            • Dimensions: State, Mon Nu, Year Nu
            • Fact: response goal
      2. Joins
        1. dlyFactTable is joined to received messages
          table on : State,Type, Yr_Nu, Mo_Nu
        2. dlyFactTable is joined to response goal table on
          : State,                 Mon Nu, Year Nu
      3. The Goal is to calculate the following three
        measures at State Level, and Region Level. The reporting is at month level.
        1. State Level:
          1. Monthly Response Score. Formula: ((responses/(received messages*response goal))*100)
          2. Monthly Post Score. Formula: ((posts/(received messages*response goal))*100)
          3. Overall Score for State: ((([Response Score]*1.5)+([Post Score]*2))/50)

       

      1. Region Level:
        • Overall Score for Region: Avg of Overall Score at individual state level

      For example, Overall score for East Region = (Overall score for New York + Overall score for New Jersey)/2

       

       

      I can calculate the state level scores. However, I am not sure how to calculate the Region level scores given it involves LOD and then aggregation on top of it.

      The problem with using window function is on my region level score view, I don’t want to display state level information.

       

      I have attached a tableau workbook in which I have calculated all individual scores and need to find a way to calculate the overall score at Region level. Basically what I am looking is to calculate and display the metric named as ‘Desired-OverallScore’.


      However I want it to be displayed at Region level without the State dimension showing in the view. So below is what the final desired view for West Region:

      Month

      Region

      Desired-OverallScore

      Mar-17

      West

      0.071848073

      Apr-17

      West

      0.005554036

       

      Any help will be greatly appreciated. I have attached my workbook with sample data. Please note it is sample data so it won’t make much sense.