9 Replies Latest reply on Dec 4, 2012 9:39 AM by Sarah A

    Is it possible to sum the table values given for each partition?

    Sarah A

      I have a list of people who either signed up or signed out to receive newsletters.  I want to know for each month, what is the total number of people whose last known status was "signed up" (so if they signed up to receive a newsletter in March, than signed out in April, their status as of April should actually be "signed out" and they shouldn't be counted).

       

      I've created various different table calculations to do this using "Customer Name" as the partition.  Table equations work fine at the customer level, the problem is that I don't actually care about the individual customers.  Instead I want to know the sum of customers whose last known status was "signed out".

       

      Is there anyway to do this (to basically get the sum of a table calculation across all partitions)?

       

      I can give a sample workbook if it will be helpful!  I thought it might be more useful to stay general.

        • 1. Re: Is it possible to sum the table values given for each partition?
          Jonathan Drummey

          Hi,

           

          Generally, what you'll do in a situation like yours where there are two dimensions of interest - Month and Customer in your case - is that you'll have a Compute Using set to one of them for the initial calcs (like Month in your case, partitioning on Customer), then the totaling calc will have the Compute Using set to the other Dimension, with the inner nested calc still having the original setting.

           

          I've set up an example in the attached, where MAX([Category]) = "Technology" is a stand-in for "signed out". The calculation to determine the most recent status has a Compute Using of the Order Date, while the final calculation that generates the total by month has a Compute Using of the Customer.

           

          If you have more questions, let me know. BTW, when dealing with table calculations, posting a packaged workbook with sample data is almost always the more helpful option. There are so many factors (shape of the data, pill types, where the pills are in the view, addressing/partitioning, sorting, at the level, restarting every, choice of mark type, ignore in table calculations setting, show missing values setting, etc.) that it's generally easier for me to identify what is going on by looking at the workbook, faster for me to help you because I don't have to interpret your post to try to build a workbook with an example, and quite a bit faster faster for you to write a basic description and post the workbook than trying to document every setting and location in your post.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Is it possible to sum the table values given for each partition?
            Sarah A

            Thank you Jonathan for the answer!!

             

            The problem is that there isn't a complete set of months associated with each name.  For instance, Blossom signed up in June.  Since she never signed out, she should still be counted among the July totals ( but she doesn't have a July row of data)

             

            I'm not sure how to solve this.  I tried creating an equation for each month (looking for the last date before that month), but I wasn't successful (I can't figure out how to get "last" before a specific date) and anyway I would need to then create a parameter so the values could be filtered by date.

             

            Do you have any ideas how to get the complete values for each month (even if a person doesn't have a row associated with their month).  It seems like the only option at this point might be to go back and restructure the original data so there is a row for each month for each person.

             

            Thank you so much for your previous workbook!  Even if it doesn't solve this problem, it really cleared up a lot for me.

             

            -Sarah

            • 3. Re: Is it possible to sum the table values given for each partition?
              Jonathan Drummey

              Hi Sarah,

               

              In the workbook I'd posted I'd turned on the show missing values setting for the month, this causes Tableau to begin to pad the missing months, and then the table calculations could fill in the values. If you post a packaged workbook (.twbx), I can help set that up for you.

               

              Jonathan

              • 4. Re: Is it possible to sum the table values given for each partition?
                Shawn Wallwork

                [So Jonathan, how's that 'I'm not answering any more questions this morning' resolution coming? Oh wait, it's afternoon! You're good to go. Carry on, don't mind my interruption.]

                 

                --Shawn

                • 5. Re: Is it possible to sum the table values given for each partition?
                  Jonathan Drummey

                  Shawn - too much time waiting for database queries leads to checking my Inbox.

                  • 6. Re: Is it possible to sum the table values given for each partition?
                    Shawn Wallwork

                    For me it's just that pleasant chime I've set to alert me. Hummm, maybe I'll get more done if I change it to an annoying HONK!

                     

                    I'll get back to you....

                     

                    --Shawn

                    • 7. Re: Is it possible to sum the table values given for each partition?
                      Sarah A

                      Oh thanks again!   

                       

                      I added another column which had values for all the months and hid it and that seemed to partially work (I couldn't make the missing months appear just by selecting "show missing values". 

                       

                      That worked for the months that still had data, but the months in between still have zeros.  In the workbook I attached June 2011 to May 2012 all have 0s, but they should actually show 3 because nobody signed out during those months (so by default all the users remained signed in).

                       

                      I think this might be because in the equation "previous value" only works if the month before has been populated?  I tried to create a new equation using last(), but with no success.

                       

                      Thanks again for your help!!

                      • 8. Re: Is it possible to sum the table values given for each partition?
                        Jonathan Drummey

                        Hi Sarah,

                         

                        In going through your worksheet, there were at least two issues I could see. One was that your Current Status calc didn't have it's Compute Using not properly set. The calc to determine current status needs to have a Compute Using of the date so that it is calculated for each date (partitioning on Full Name), and then that calc is nested within the WINDOW_SUM calc that has its Compute Using set to the Full Name/Customer so that it is calculated for each customer. The Total by Month calc doesn't need the embedded IF statement because the Current Status calc is only returning 1 or 0.

                         

                        See the attached for details.

                         

                        Jonathan

                        • 9. Re: Is it possible to sum the table values given for each partition?
                          Sarah A

                          Thank you!  That worked perfectly! 

                           

                          I copied your book exactly, but for some reason, the calculations didn't come out correctly until I dragged all the measure values of the workbook and then dragged them back in.

                           

                          Thanks again!