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

# Summing Two periods

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.

• ###### 1. Re: Summing Two periods

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

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

• ###### 2. Re: Summing Two periods

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!!!

• ###### 3. Re: Summing Two periods

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

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.

• ###### 5. Re: Summing Two periods

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

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

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.

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?

• ###### 8. Re: Summing Two periods

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

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

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

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

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

So if you have a view like the below:

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?

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

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

1 2 Previous Next