4 Replies Latest reply on Dec 2, 2013 5:41 AM by Egor Ushakov

    Table calculations with data/fields not presented in a view

    Egor Ushakov

      While basic table calculation concept is clear (do hope!) for me, now I'm stuck with approaches on how to perform table calculation on pretty simple crosstab view (see tbwx attached).

       

      First of all, Quantity values summation within each MeetingID. At first I decided to use this technique http://kb.tableausoftware.com/articles/knowledgebase/removing-duplicated-data-after-join . But as you know we can NOT do subsequent aggregation over already aggregated data. OK, leave this summation to database engine and grab aggregated values with Custom SQL (option not presented in tbwx attached). Great, let's name the fact value received from database as ContactSales. I still don't want to aggregate ContactSales value over Date because I would like Tableau do this job for me. But now I need to calculate AVG(ContactSales) for given MONTH(Date) for each ContactID. How it can be done since ContactID not presented in a view even due to technical restrictions (tens of thousands records)?

       

      And finally I need to call WINDOW_AVG(SUM( of average ContactSales for each contact for each month)), -2, 0). That is I need to make some aggregation calculation before sending values to WINDOW_AVG. But how to calculate average of ContactSales if I have no ContactID field in a view?

       

      Hope attached picture can clarify the issue. I think I lost something obvious but very important for solving the problem. Please point me in the right direction.

       

      BTW, what about initial aggregating Quantity values inside Tableau? Is it possible in this use case or database pre-aggregation is a single possible solution?

        • 1. Re: Table calculations with data/fields not presented in a view
          kettan

          Hi Egor,

           

          I think we are battling the same fundamental Tableau issue as so nicely formulated by  Richard Leeke:

          The reason that is hard for the left hand view is that you are wanting to filter [calculate] on an aggregate at a different level of detail to the level shown in the viewSource

           

          Therefore I recommend you to follow the answer in  Count claims with reserve (exclude claims with no reserve)  which I hope will come from the table calc expert  Jonathan Drummey.

           

          Johan aka Kettan

          • 3. Re: Table calculations with data/fields not presented in a view
            Jonathan Drummey

            Hi,

             

            I looked at this and have a few questions based on your screenshot and the attached workbook?

             

            A. For #1, what does "within each MeetingID" mean? Can we just SUM(Quantity) within the combination of Date/Assigned User ID/Medicine/Contact ID, or if you need the SUM(Quantity) at the level of MeetingID, what kind of aggregation would it later have?

            B. For #2, in the attached workbook "Resulting Crosstab View" it had Medicine as a dimension on Rows. So should the average values of #1 actually be the average values for each Contact ID within each Month, Medicine, and Assigned User ID?

            C. For #3, depending on how you answer A & B, there may be multiple calculations involved instead of a single WINDOW_AVG.

             

            Jonathan

            • 4. Re: Re: Table calculations with data/fields not presented in a view
              Egor Ushakov

              Hi Jonathan,

               

              Guess I was wrong not describing the issue in a whole. I just wanted to get an idea to build solution by myself not overloading with details. Moreover while my English to so bad for everyday speaking I think I'm often wrong with prepositions and conjunctions discussing dimensions, measures, grouping, aggregations, etc. It was really hard for me to deal with "along", "within", etc. while studying table calculation concept. Pictures helped a lot!

               

              Now I try to describe complete task to be solved.

              A. For #1, what does "within each MeetingID" mean? Can we just SUM(Quantity) within the combination of Date/Assigned User ID/Medicine/Contact ID, or if you need the SUM(Quantity) at the level of MeetingID, what kind of aggregation would it later have?

               

              AssignedUserID on each MeetingID with ContactID may collect the following information:

              • ContactID assigns Medicine (AAA) for Nosology (AAA_1) in the amount of (Quantity) 10 monthly
              • ContactID assigns Medicine (AAA) for Nosology (AAA_2) in the amount of (Quantity) 12 monthly
              • ContactID assigns Medicine (EEE) for Nosology (EEE_5) in the amount of (Quantity) 5 monthly


              That is the fact information AssignedUserID writes to source database. Now I need to sum all the Quantities grouped by Medicine to receive something like (let leave Nosology field for now):

              • ContactID assigns Medicine (AAA) in the amount of (Quantity) 22 monthly
              • ContactID assign Medicine (EEE) in the amount of (Quantity) 5 monthly

              Here's the fact I should start further calculation from. (Don't forget this fact information relates to a Date of collection!)

               

              Those meetings with ContactID can happen from zero up to 2 times (or even 3 maximum) a month. If it happened one or more times I need average value. If zero - get a previous value but not futher then 3 months ago: ContactID is considered inactive if visited more then 3 month ago and should be thrown out of any calculations, i.e. paste 0 value in a proper month. Thus we have a series of average Quantity values for each month for definite ContactID. In fact I need to "draw" a virtual line chart for every ContactID for each Medicine where we have MONTH(Data) dimension on Column shelf, AVG(Quantity) on Rows shelf and Medicine on Color shelf.

               

              After we have "lines" of Quantities for each ContactID I need to apply WINDOW_AVG with 3-months window size (from -2 to 0) to reduce different inaccuracies and finally sum all the results for every AssignedUserID so as we could say something like this:

              "All the ContactIDs (note plural here!) who are "owned" by AssignedUserID prescripted:

              • in January 2013
                • Medicine AAA in the amount of ..., Medicine EEE in the amount of ..., and Medicine FFF in the amount ...
              • in February 2013
                • Medicine AAA in the amount of ..., Medicine EEE in the amount of ..., and Medicine FFF in the amount ...
              • etc.

               

              B. For #2, in the attached workbook "Resulting Crosstab View" it had Medicine as a dimension on Rows. So should the average values of #1 actually be the average values for each Contact ID within each Month, Medicine, and Assigned User ID?

              AssignedUserID relates to ContactID with "one-to-many" relationship. Whole calculation is intended to get "performance"/"productivity" of ContactIDs that belongs to a given AssignedUserID.