3 Replies Latest reply on Dec 14, 2016 8:21 AM by Corey Brown

Creating Columns for Current Month, R3, R3, and R12 in a Crosstab

I would like to see my measure on the rows and my time periods (Current Month, R3, R6, R12) on columns.  This has proven to be more difficult for me to do that I had thought.  I have attached a work book with 2 ways I have tried this and neither work.  The first example is exactly how I would like to view the data, however, for R6 and R12, the numbers aren't calculating correctly.  See the tab "Why example 1 Doesn't Work".  It appears it is only including the months in R6 that are not being included in R3, and R12 only includes months that are not included in R6 and R3.  Attempting to work around this, I came up with "Example 2" in which I created 4 calculated fields for Current Month, R3, R6, and R12.  This also did not turn out right.  Any help would be much appreciated!

• 1. Re: Creating Columns for Current Month, R3, R3, and R12 in a Crosstab

Hi Corey,

Thanks for preparing a thorough example.

Let's start with your 'Why Example 1 Doesn't Work' tab. Your 'Time Period Combined' calculation is creating a dimension which is evaluated on a row by row basis. Each date may only fall into one of those classifications, it's not possible to fall into multiple.

There's a few solves for this;

1. Create a calculated field for each of your buckets. Similar to your current 'Time Period Combined' calc, except you create one calculated field per bucket.

I.e. CurrentMonth = SUM(IF DATEDIFF('month',[Order Date],Today()) = 0 THEN Amount END)

R3 = SUM(IF [Order Date]>= DATETRUNC('month',DATEADD('month',-3,TODAY())) AND [Order Date]<DATETRUNC('month',DATEADD('month',0,TODAY())) THEN [Amount] END)

etc etc

Then you add the measure values to the text button on the marks card, drag measure names to columns and boom; The other way is to create a running sum which ignores your 'Current Month'. Create a calculated field as follows:

IF ATTR([Time Period Combined])!='Current Month'

THEN RUNNING_SUM(SUM(IF [Time Period Combined]!='Current Month' THEN [Amount] END))

ELSE SUM([Amount])

END

When you drop it into your measure values or on the column shelf, you need need to right click, select Compute Using, Table Across so it knows how to calculate the running sum.

1 of 1 people found this helpful
• 2. Re: Creating Columns for Current Month, R3, R3, and R12 in a Crosstab

I don't have Tableau available so can't open your workbook. Have you tried the formula below?

R3 IF (DATEDIFF('month',[Order Date],TODAY()))<=3 THEN [Sales] ELSE null END

R4-6 IF (DATEDIFF('month',[Order Date],TODAY())) > 3 and (DATEDIFF('month',[Order Date],TODAY())) <= 6  THEN [Sales] ELSE null END

R7-9 IF (DATEDIFF('month',[Order Date],TODAY())) > 6 and (DATEDIFF('month',[Order Date],TODAY())) <= 9  THEN [Sales] ELSE null END

R10-12 IF (DATEDIFF('month',[Order Date],TODAY())) > 9 and (DATEDIFF('month',[Order Date],TODAY())) <= 12  THEN [Sales] ELSE null END

• 3. Re: Creating Columns for Current Month, R3, R3, and R12 in a Crosstab

Thanks Tom!  That was exactly what I needed.