2 Replies Latest reply on Dec 17, 2011 4:13 PM by Joe Mako

    80% of Revenue Flag

    Marc Engle

      Hello.  I'm having trouble getting a calculated field to work as I want it to.  My data source is parameter value based and contains revenue dollars and units sold for each product in a given fiscal period for the company.  It is also broken up into what type of data the record is (billings, backlog, past due). 

       

      Please see the attached workbook.  On the Bar Graph sheet, I'm able to get a running % of total calculation to work correctly so it shows me how many products make up the top 80% of revenue for the given period (intersection of the 80% line with the running % of total line).  On the Scatter Plot sheet, it shows a mark for each product that had actual or planned revenue during that fiscal period at the point in time when the product released to production and at value of the sum of dollars for that period.  What I'm wanting to do is to make the marks for the products that made up the top 80% of revenue in that period stand out somehow (could be different size, different shape, etc).  The color still indicates whether the record is billings, backlog, or past due data.  For the current period (the one that potentially contains billings/backlog/past due for each product), I also want to have the mark size be different if the sum of the dollars for the product (regardless of type) make up the top 80%.

       

      I have tried many different partitioning and addressing schemes, but cannot figure out how to get that calculation to work right.  Can anyone help?  Please let me know if you have any questions.

        • 1. Re: 80% of Revenue Flag
          Richard Leeke

          I had a bit of a look at this and kept running into the restriction that you can't sort or partition a table calculation by the results of another table calculation.

           

          It would be interesting to know if Joe's bag of tricks includes any techniques which would help here (I think Joe's a bit busy, so he's not very active on the forum at the moment - so I thought I'd see if I could pique his interest).

          • 2. Re: 80% of Revenue Flag
            Joe Mako

            The limitation this situation faces is not using the result of a table calc to sort another table calc. The limitation here is lack of control of the addressing sort order. This is a similar situation as Re: .Top X shows more than X values when using the color shelf

             

            Tableau does not offer the configuration options you would need to get the results you are looking for. I highly recommend you contact Tableau support and request the ability to have multiple dimensions in the compute using side, but instead of using their concatenation for sorting, use just one dimension from the compute using for sorting. This is like the reverse of http://kb.tableausoftware.com/articles/knowledgebase/nestedsorting you want a non-nested sorting when using multiple dimensions for compute using.

             

            I very much so like your Bar Graph sheet, I really like how you are using the multiple mark types to get the two levels of aggregation at the same time.

             

            So here is the current workaround:

             

            1. using the formula

            TOTAL(SUM([Dollars]))
            multiple times with difference compute using settings to get the Product's percent at the Product-Type combination level. (this was just my first pass, thinking about this, this step and formulas could be written in a few different ways)

            2. use a modification of Richard's formula (like rolling your own running_ function):

             

             

            IF (ATTR([Product])==LOOKUP(ATTR([Product]),-1)) THEN
            
                 PREVIOUS_VALUE(0)
            ELSE
                PREVIOUS_VALUE(0) + [Percent]
            END
            


             

            3. Create the Table View worksheet. Notice that Period is on the Filter shelf, and not the Pane shelf, along with PR Date is using the ATTR aggregation (this works because each Product-Type combination only has a single PR Date value).

            4. use the "Show Me" to create another view, scatter, stacked bar, the ones I tried have the same effect. This is currently the only known method to get the addressing sort order desired.

            5. arrange the pills as on the Final View

             

            If you wanted to include the first one over 80% as well in the top, that would likely be another formula like Richard's, but I haven't worked it out yet - I'll leave that brain teaser for you Richard :)

             

            There is understandably a lot going on in this workbook. If I was to create a test that evaluated if someone was a Tableau expert, this would be one of the questions. It does not use all the possible concepts at play when using table calculations, but it certainly requires awareness of quite a few.