1 2 Previous Next 15 Replies Latest reply on Aug 7, 2015 7:59 AM by Trong Bo

    Summing Two periods

    Trong Bo

      Hi,

       

      I've been racking my brain on this one.  I'm trying to build a table displaying sales for the last 12 periods (months).  That part is easy enough.  The next part is I need to have a calculated field that sums the sales for the given month plus the previous month.  So December would give me a total for (December + November) Sales,  November would give me a total for (November + October) Sales, etc.  In the format below, I have the single month sales.  I've attached the packaged workbook using the Sample Superstore data.

       

      Two Period Sum.png

        • 1. Re: Summing Two periods
          pooja.gandhi

          Create a formula as shown below, take off the sum(sales) from the marks card and instead place this formula on the text.

           

          Capture.PNG

           

          Also make sure to click the little arrow on the calculation you place on the text to set it to compute using - table (across).

           

          Capture.PNG

          • 2. Re: Summing Two periods
            Trong Bo

            Thank you for the quick response, Pooja.  This gets me much closer to what I need; however, after doing exactly as you suggested, my calculated field is skewed to the wrong month.  So for my December calc of (December + November sales) the field is displaying under November.  November total is displaying under October, etc.  Do you know how to fix that?  Many thanks!!!

            Two Period Sum 2.png

            • 3. Re: Summing Two periods
              Trong Bo

              Oh.  I got it.  I switched the (-1) to (1) in the lookup and that skewed it the way I needed.  Thank you!  Brilliant!!!

              • 4. Re: Summing Two periods
                pooja.gandhi

                Oh sorry about that, I just noticed you had months going from dec to jan and not jan to dec. If you change the formula to

                sum([Sales])+lookup(sum([Sales]),1) it should work as expected.

                 

                Capture.PNG

                • 5. Re: Summing Two periods
                  Trong Bo

                  Pooja,

                   

                  Have you ever done a DSO (Days Sales Outstanding) dashboard?  The problem you just helped me with is for my secondary data source.  When I use the 2-period-Sum from the secondary data source and blended to my primary data source, the sum becomes very high.  Any ideas why that occurs?  Can I not bring that calculation into my DSO calculation on the primary data source?  Thank you!

                  • 6. Re: Summing Two periods
                    pooja.gandhi

                    Hey Trong,

                     

                    It is not too easy to suggest something without having the data at hand. Are you able to post a sample packaged workbook? If you data is confidential you can see this:

                     

                    Anonymize your Tableau Package Data for Sharing

                     

                    and anonymize your data for sharing.

                     

                    Thanks,

                    Pooja.

                    • 7. Re: Summing Two periods
                      Trong Bo

                      Hi, Pooja.

                       

                      I've attached the workbook.  The image below is basically what I'd like the end result to look like.  It's pretty close except the 2 period sum currently being used is actually the "current" 2 period sum being repeated for each month.  If you drop the "Previous 2 Periods Revenue" from the secondary source onto the Marks shelf, you will see it is the same for each month.  What I would like is for the solution you came up with yesterday to replace that value.  I created the calc "Sum 2 Periods" to reflect that, but when I swap that calc in the "DSO Historical" calculation, the sum is too high.

                       

                      DSO ws1.png

                       

                      Another issue I'm seeing is that on the secondary data source, the final month doesn't get a 2 period sum.  Obviously because it is the last column and there is no adjacent column to add.  How can we fix that so that it does get a sum?

                      DSO ws2.png

                      • 8. Re: Summing Two periods
                        pooja.gandhi

                        Hey Trong,

                         

                        I am not still sure what exactly do you need. I feel like part of the problem is the way you are blending the data source. You are blending on facility name, I feel like it should be blended on a date field. But I am not sure which date in the primary source matches which date in secondary source? You might want to further look into blending that field.

                         

                        To answer another part of your question, you say you fix that the last column does get a sum? sum of what? You mean if there is no column or no date prior to that field just keep the sum as its own sum? I don't get it. I am sure if you clearly explain, me or someone else will be happy to assist.

                         

                        Thanks,

                        • 9. Re: Summing Two periods
                          Trong Bo

                          Hi, Pooja.

                           

                          The fields I'm blending don't seem to be making much of a difference at this point.  If I blend on just (FacNameCrosswalk = Facility Name) or both (FacNameCrosswalk = Facility Name) and (Period = Formatted Date), I get unexpected results either way.  My thinking here is that I want to match each facility name on the primary to all of the matching facility name records on the secondary and let the "columns" handle the grouping of the dates.  (Correct me if I'm wrong there.)

                           

                          For the "Sum 2 Periods" calculation on the secondary source, we typically show a 13 month trend on all of our tables.  So for example, if my data were current, I would expect to see from left to right (July 2015 - June 2014).  So in order for June 2014 to have a 2-period sum, I would likely need to have data for May 2014 (but I don't want to display May 2014). 

                           

                          In the sample I uploaded, the sheet "Two Period Sum" shows the correct Sum of Amount for each month and also the correct "Sum 2 Periods" for each month (except for February).  On the other sheet "DSO by Facility," if I plug in the "Sum 2 Periods" calculation into the "DSO Historical" calculation, it seems to be using the active table (the table on "DSO by Facility") for the table calculation and not the table that the calculation was created in.  Is that what is happening?  How do I correct that?

                           

                          Thanks,

                          Trong

                          • 10. Re: Summing Two periods
                            Trong Bo

                            So, I was able to figure this out.  I swapped my data sources, making the Primary source the Secondary source and vice versa.  I still have no way to perform the 2-period sum for the right-most column because we're telling Tableau to perform a Table-Across calc, and since the right-most column has no other column to add, it just displays nothing.  But most of the table works.

                            • 11. Re: Summing Two periods
                              pooja.gandhi

                              Hi Trong,

                               

                              Sorry I didn't get back to you. Maybe I read the email when I didn't have access to tableau and it skipped my mind to respond at another time. Thats great! I believe I had tried swapping the data sources as well, but I wasn't sure if swapping was an option for you. If the table calc is performing a table across calc and the last column doesn't have any other value to add, what value do you want the last column to contain? Which was also my second question to you on the earlier post.

                               

                              Thanks,

                              Pooja!

                              • 12. Re: Summing Two periods
                                Trong Bo

                                There will likely be a column (for the prior period) that won't be displayed.  So if the last column is March 2015 in the table (and it has underlying data), but it needs February 2015 in order to perform the Table Across calc (which is in the data source but not displayed in the table).  What "work-around" would we need to show the DSO for the last column?

                                 

                                Thanks,

                                Trong

                                • 13. Re: Summing Two periods
                                  pooja.gandhi

                                  So if you have a view like the below:

                                   

                                  Capture.PNG

                                   

                                  And create a calc like: last() and place it in filters and choose range 1-4 in the pop up, February disappears but it will still perform the table calculation; Is this what you mean?

                                   

                                  Capture1.PNG

                                  1 of 1 people found this helpful
                                  • 14. Re: Summing Two periods
                                    Trong Bo

                                    That's a very interesting solution!  It does the job.  Thank you, Pooja!

                                    1 2 Previous Next