3 Replies Latest reply on Apr 4, 2018 1:08 PM by Michael Niven

# How do I write the following without FOR statements?

So let's say I have a dataset with the following:

COLUMN A               COLUMN B

Fiscal Period             Total Sales

201801                      1

201802                      3

201803                      19

201804                      5

201805                      6

I am trying to create 3 calculated fields that sum the above as follows:

• Latest Period
• Last 4 Periods
• Year-to-Date

How would I get it to do this? I noticed Tableau doesn't seem to have FOR statements. Below is my thought process if FOR statements were possible (feel free to correct me):

1. Create a Calculated field, "Max Period".
{Fixed:max([Fiscal Period])}

Latest Period:

FOR(INT([Max Period]) = INT([Fiscal Period]))

SUM([Total Sales]

Last 4 Periods:

FOR(INT([Fiscal Period]) >= [Max Period] - 3)

SUM([Total Sales])

Year-to-Date

FOR(LEFT([Fiscal Period]),4) = "2018")

SUM([Total Sales])

I think this logic is right, but I'm wondering how to get it to translate this correctly into Tableau? My desired output (well, a very simplified version) is as follows:

Latest PeriodLast 4 PeriodsYear-to-Date
63334
• ###### 1. Re: How do I write the following without FOR statements?

hi,

can you send sample workbook or data

• ###### 2. Re: How do I write the following without FOR statements?

Latest Period

IF {Fixed:max([Fiscal Period])}=Fiscal Period THEN Total Sales END

Last 4 Periods

Fiscal Period>=({Fixed:max([Fiscal Period])}-3) AND Fiscal Period<={Fixed:max([Fiscal Period])}

Year To Date

if YEAR(Fiscal Period)={Fixed:max(year([Fiscal Period]))} then [Value] END

• ###### 3. Re: How do I write the following without FOR statements?

I unfortunately cannot, it is sensitive data which is why I tried to write out a simplified version of what I was looking to achieve (see table for a very basic view). Assume those two columns listed above were the dataset.

Sorry