2 Replies Latest reply on Apr 25, 2013 6:40 AM by John Bencina

# Creating single calculated value per state?

Trying to wrap my mind around this problem

• I have a workbook which has 50 rows down (one for each state)
• I'm filtering for the trailing 4 weeks of data
• I want to show last week performance vs 4wk average such that my table is 2 columns (AL +3%, AR -6%, AZ +2%) and so on.

I'm new to Tableau, so from my current understanding, Tableau cannot see the data unless its part of the table. Therefore what I would have to do is:

• Make the table 50 rows down for each state
• Put the date into columns such that there are four across
• Create a calculation which works table (across)
• Hide the first three columns

Now I can do these above steps successfully, but is that the best way to accomplish this? If I update with new data, will I have to manually hide/unhide the next week? Is there a way to automate that?

The goal is to have a single US State Map which shows this single percentage value.

• ###### 1. Re: Creating single calculated value per state?

Hi John,

One way that this can be done is to create three calculated fields--one to find the values of the last 4 weeks, one to find the values of the last week and the percent difference.

Order Quantity of Last 4 Weeks:

if [Order Date]>=DATEADD('week', -4, today())  and [Order Date]<=today() then [Order Quantity ] end

Order Quantity of Last Week:

if [Order Date]>=DATEADD('week', -1, today())  and [Order Date]<=today() then [Order Quantity ] end

Percent Difference:

(sum([Order Quantity of Last Week])-sum([Order Quantity of Last 4 Weeks]))/sum([Order Quantity of Last 4 Weeks])

Hope this helps!

-Tracy

• ###### 2. Re: Creating single calculated value per state?

Thanks!