1 2 Previous Next 15 Replies Latest reply on Jul 14, 2011 6:00 AM by Wim Van Looy

    How do I calculate a field for each day (that is repeated for each item)?

    Martin Olivas

      My data includes date, item, amount and volume.  So an example of my data:

      Date    Item        Amount    Volume

      7/5/11    x    1    5*

      7/5/11    y    2    7*

      7/5/11    y    3    7

      7/5/11    z    1    10*

      7/5/11    z    2    10

      7/5/11    z    3    10

                 

      7/6/11    x    1    6*

      7/6/11    x    2    6

      7/6/11    x    3    6

      7/6/11    y    2    5*

      7/6/11    z    3    10*

      7/6/11    z    4    10

                 

      7/7/11    y    2    15*

      7/7/11    y    4    15

      7/7/11    y    5    15

      7/7/11    z    6    15*

      7/7/11    z    7    15

      7/7/11    x    8    10*

       

      I am totaling for each date, summing up the amount (sum(Amount)) and taking the max volume (Max(Volume)) for each day.   

       

      So each day would look like:

      7/5/11    Total    12    22

      7/6/11    Total    15    21

      7/7/11    Total    32    40

       

      This works as long as we only have one item.  However, my customer wants to be able to select multiple items (using a quick filter.) For each day each item has a volume assigned and I want to be able to sum up the days volume for each item.  I have attached a tbx file with some test data and a png file that shows how we would like the data to be displayed.  Any help would be appreciated.

        • 2. Re: How do I calculate a field for each day (that is repeated for each item)?
          Martin Olivas

          Hi Richard,

           

          That's how I have it set up now and it works for comparing the items but the customer wants to be able to sum up the values (amount and volume) for all items.  So, in your solution, you remove item from the columns shelf.  The amount will sum up correctly but the volume will be incorrect since it is just taking the max().  We want it to sum up just one volume for each item.

           

          Thanks again

          Martin

          • 3. Re: How do I calculate a field for each day (that is repeated for each item)?
            Richard Leeke

            OK, I've just re-read your original question and realised what the asterisks were indicating.  It never occurred to me that you wanted to take the sum() of the max() per item because I can't imagine how that could be a meaningful result.

             

            Nonetheless, if that's what you want, it's easy to do with  table calculations.  I'm just going out now but will post an updated workbook later.

             

            In the meantime, since I'm intrigued, can you explain enough about the context for me to see how that's a useful result.  I guess I can kind-of imagine how it might be.

            • 4. Re: How do I calculate a field for each day (that is repeated for each item)?
              Martin Olivas

              sure, its just how we have it defined in the db.  We have an amount (that gets accumulated over a day) and the volume indicates how much that item is able to handle on a particular day (which is defined at the item level.) 

               

              Date Item Amount Volume

              7/5/11 x 1 5*

              7/5/11 y 2 7*

              7/5/11 y 3 7

              7/5/11 z 1 10*

              7/5/11 z 2 10

              7/5/11 z 3 10

               

              Since the volume is duplicated for every amount, if there is only one item we can use the max() function to get the result.  However, this falls apart when more than one item is selected. 

               

              So our customer wants to see a comparison of what the item produced versus how much it could have produced.  Hope that adds makes sense and adds context. 

              • 5. Re: How do I calculate a field for each day (that is repeated for each item)?
                Richard Leeke

                OK, makes sense.  I should have spotted that the value was the volume was the same for all rows for an item on each day.

                 

                Here's an updated version which does what you want with table calculations.

                 

                To understand tab;e calculations you need to look at both the definition of the calculation itself, and the addressing/partitioning  (from the "edit table calculation" option on the pill menu for the field on the shelf.

                 

                I've defined 3 calculations: Total Amount, Total Volume and First Row?.  Have a look and see if you can work out what is happening - otherwise let me know and I'll explain some more.  The filter on First Row is just to avoid displaying multiple sets of the same results on top of each other, as described in lots of places on the forum.

                • 6. Re: How do I calculate a field for each day (that is repeated for each item)?
                  Martin Olivas

                  Richard, ok I applied what you have into my report and it works.  However, I have no clue as to why, LOL.  If you have a chance could please explain what you (we) did.  Thanks for your help;

                  • 7. Re: How do I calculate a field for each day (that is repeated for each item)?
                    Joe Mako

                    The method used in Richard's workbook works in this situation because of the data contents, not because of structure. If the underlying values change, then it is quite possible that method Richard used would stop working.

                     

                    Not to long ago, I used and told others to use this less than ideal method, since then I have learned that this approach is not ideal. Additionally, the difference between the method I will explain, and the one Richard used feels like a minor detail, but has an impact. As with many things there are more than way way to accomplish desired results in Tableau, but sometimes the results we get are not resilient to changes in the data.

                     

                    In the attached workbook "item_rl2_with filter.twbx", I added a filter to remove one date and item combination, and while the numbers are correct, you can see Tableau no longer plots as desired. A number of things could be done to rearrange this and make it work, but I prefer the following method:

                     

                    1. Start with a fresh workbook with just the fields: "Date", "Item", "Amount", "Volume"

                    2. I Changed the Data Type for the "Date" field to Date instead of Date Time (this is optional)

                    3. Place both "Date" and "Item" on the Rows shelf, Discrete and All Values (a shortcut is a right-mouse button drag-n-drop)

                    4. Make it so "Amount" and "Volume" fields are displayed as text in columns, as in the the attached image "setup1.png"

                    5. When you change the aggregation of the "Volume" pill from SUM to AVG, you will have the base values that you want to aggregate to the Date level.

                    6. To add up all those "SUM(Amount)" values, you can use an aggregrate of an aggregate function like:

                     

                     

                    IF FIRST()==0 THEN
                    
                     WINDOW_SUM(SUM([Amount]),0,IIF(FIRST()==0,LAST(),0))
                    END
                    


                     

                    You will notice that I have an "IF FIRST()==0 THEN" and "END" in this formula, that is because we only want one mark per date, so we will only return the desired value for the first record, and return Null for all others.

                     

                    7. When you place this calculated field on the Measure Values shelf (with the "Amount" and "Volume" pills), Tableau uses a default Compute using, and it is generally not what we want (unless to specify the default when editing the calculated field).

                    8. Since you want the sum per date for all items that day, another way to say that is you want to partition on "Date" and use "Item" for addressing.

                     

                    When you set "Item" as the Compute using value, you are telling Tableau to address this calculation on "Item" and partition on all other dimensions, and in this case, "Date" is the only other dimension in use on the worksheet so "Date" is used for partitioning.

                     

                    9. Next create the calculation to sum up the average "Volume" values, this is just like the function you had originally created:

                     

                     

                    IF FIRST()==0 THEN
                    
                     WINDOW_SUM(AVG([Volume]),0,IIF(FIRST()==0,LAST(),0))
                    END
                    


                     

                    Again we are only going to keep one mark per date. The WINDOW_ functions have the advantage of being able to specify a variable or conditional addressing range to partition on, that is the two offset arguments:

                     

                     

                    0,IIF(FIRST()==0,LAST(),0)


                     

                    0 means current record, and LAST() means last record in partition. so on the first record, the range for the WINDOW function will be from the current (first) to the last, and for every other record in the partition, the range will be from 0 to 0 (current to current). This is something Richard developed, and I use if every day.

                     

                    10. Place this calculated field also on the Measure Values shelf, and with the Compute using also set to "Item" (so Tableau will address on "Item", and partition on "Date"), you will have something like the image "setup2.png"

                     

                    At this point you can see your six values of interest. Next to create the visualization of these numbers,

                     

                    Starting with a new worksheet, do the following:

                     

                    1. Create a Set from the dimensions "Date" and "Item" by ctrl-click selecting them, and from the tier right-click context menu, Create Set

                     

                    I have recently found that using Sets with table calculations makes things easier, and in this case, more resilient to changes in the data.

                     

                    2. Change the mark type from Automatic to Line, and place the "Date - Item" set on the Path shelf.

                     

                    This is desirable because the combination of "Date" and "Item" creates the records the table calculations are addressing on, additionally with Sets, all dimensions in use within the Set can be used for partitioning or addressing. Additionally, by having this Set on the Path shelf, we can use "Date" as a dimension for partitioning, and not cause the line mark to restart.

                     

                    3. Place "Date" on the Columns shelf, and you have options, you can have it be a Continuous All Values, or a Discrete Attribute. The continuous will provide you a nice date axis, and the Discrete Attribute will allow you to have discrete labels. This pill should not be a Discrete Dimension because if it is, then it will cause the lines to restart, and you end up with a bunch of dots (would be okay if you wanted bars instead of lines).

                     

                    4. Place the two custom table calculations, and set both of their Compute using setting to "Item". Even though an "Item" pill is not on the worksheet, it is available because the Set includes it.

                     

                    You will now have a view like in the attached viz.png.

                     

                    Another thing of note when using this method, is that if you now sort the Set pill that is on the path shelf, you can tell Tableau what order to connect the pills in. In this case it defaults to the correct order because "Date" is the first dimension in the Set, so it defaults to sort on "Date".

                     

                    Now as long as there is data for a data, Tableau will mark that date as desired. Although they don;t always have an advantage, I highly recommend using Set when using interesting table calculations, they have made my work with table calcs easier and more predictable.

                    • 8. Re: How do I calculate a field for each day (that is repeated for each item)?
                      Richard Leeke

                      Wow.

                       

                      Brilliant write-up Joe.  In one fell swoop you have just dragged my understanding of table calculations to a whole new level.  Again.

                       

                      Now I feel I'm only three steps behind.  Again.  ;-)

                       

                      Thanks once again for your tireless efforts to educate the Tableau community.

                      • 9. Re: How do I calculate a field for each day (that is repeated for each item)?
                        Joe Mako

                        Thank you Richard,

                         

                        I am still trying to figure it all out myself, comments like these are me just writing up my guesses, trying to push things forward. I think it would be great if some of this stuff was documented somewhere instead of just guessing.

                         

                        Another thought I had:

                        On the Columns and Rows shelf, you can place multiple pills, but on a path shelf, you can only place one pill. By using a Set, you can effectively place multiple dimension fields on the same shelf that only allows for one pill. Kind of like the Measure Value/Measure Names pills, that allow for multiple Measure fields to be in a single pill.

                        • 10. Re: How do I calculate a field for each day (that is repeated for each item)?
                          Martin Olivas

                          Joe, thanks for the excellent write up - wow, good stuff! I am still trying to wrap my head around the concept.  I tried to follow your example and was able to create the calculations sheet (mine looks just like yours.) However, I am having trouble with the viz sheet.  I followed your steps and while the pills and shelves look the same, the chart looks different.  I think I did something wrong with the date. I have attached a screenshot (and a packaged workbook but hopefully the screenshot is all you need to diagnose my problem. )

                           

                          Thanks again Joe and Richard for your help.

                          • 11. Re: How do I calculate a field for each day (that is repeated for each item)?
                            Joe Mako

                            Yes, I forgot to mention that step. See http://www.tableausoftware.com/support/knowledge-base/null-values and follow the first three steps to "Remove Null values from views" for the green "Measure Values" pill on the Rows shelf.

                            • 12. Re: How do I calculate a field for each day (that is repeated for each item)?
                              Martin Olivas

                              that did it. Thanks again for all your help.

                              • 13. Re: How do I calculate a field for each day (that is repeated for each item)?
                                guest contributor

                                Excellent write up Joe - that helps me move forwards with table calcs a great deal.

                                1 2 Previous Next