I have a Status field which changes every week from Red to Yellow or Green or from Green to Red for a given project id. Basically it could downgrade or upgrade every week.

I have data for every week on Sunday. As an example like below

Date field Status Project id

11/9/2014

Red

567

11/9/2014 (most current week) Green 353

11/2/2014 Yellow 534

11/2/2014 Yellow 353

10/26/2014 Green 567

I need to have a view in which to generate a filter having values - Upgrade , Downgrade , All

and also have two date filters ( user selectable) - the base week ( most current week) and the comparison week ( list of other weeks)

So for example when a user selects for the above example 11/9/2014 and comparison week 10/26/2104 and click downgrade

it should show Project id 567 since it downgraded from green to red status

If he clicks base week as 11/9/2104 and comparison week as 11/2/2014 and clicks upgrade it should show project id 353 since the status between those weeks for project id 353 upgraded from yellow to green

I looked at the solution and it works pretty good but in the Dashboard view when I click on Upgrade/Downgrade it should only show the corresponding rows. Currently it shows all rows. Is there a way we can filter out the other rows? I tried using actions but was not successful

There are multiple ways to tackle this, each with different tradeoffs. The first route I chose makes use of a Top 1 Set to identify the latest week, a self-data blend to create a "dynamic" parameter, and table calculations to generate the results in a single worksheet. This avoids the problems with parameters that have to be updated every time the data changes, and will still show the latest available data no matter how late that data might arrive.

Here's a brief overview of how I built it:

1) Created a view with Project ID & Date field on Rows.

2) In the primary data source, created a Top 1 Set called "Is Latest Date" on the Date field dimension using MAX(Date). The IN/OUT of the Set returns True for the latest date across the entire data set.

3) Created a "Latest Status (non-optimized)" calc with the formula IF [Is Latest Date] THEN [Status] END. This returns a dimension. Put that on Rows as an ATTR(Latest Status (non-optimized).

4) Duplicated the data connection.

5) Brought ATTR(Date field) from the secondary (duplicate) source and put that on Rows. This returns the same value as Date field from the primary, the next two steps change that.

6) In the secondary source, turn *off* the blend on Date field. You'll now see * for the ATTR(Date field).

7) Right click+drag Date field from the secondary onto the Filters Shelf, choose Individual Dates and Times, and pick a single date. You'll now see the * for ATTR(Date field) on Rows change to the chosen date.

8) Changed that filter into a Quick Filter and edited it to be a single value dropdown with no All value. We'll still see the Null value no matter what, that's a Tableau behavior that can't be changed.

10) Now that we have the Latest & Selected statuses, we need to be able to compare them. The view requires the Date field to be a dimension for the calc in step 7 to work, so we can't just use a regular aggregate calc to compare the two. In order to do a comparison across the marks, we can use table calculations to "return" the values of the Latest & Selected Statuses to a given mark (address aka row in the partition), and with everything we need in the same place do the Downgrade/Upgrade computation.

Put both of the calcs on Rows and set the Compute Using of the calcs is set to the Date field. The WINDOW_MAX() is going to return the non-Null value of the given Status to every address in the partition, then the IF LAST()==0 says to only do that for the last (latest) address in the partition. With the Compute Using on the Date field, the calcs are partitioned on the Project id so only return a result for the latest date in each partition.

11. Now to create the Downgrade/Upgrade calc. This one includes all the edge conditions, so far as I can tell:

12. Now to create the final worksheet. Duplicate the workout view, drag the Date field from Rows to the Level of Detail Shelf, move all other unneeded fields from Rows out of the view, and drag the Downgrade/Upgrade field to the Filters Shelf and filter for the desired value:

Ok, now that I'm done typing that out, I just came up with another way to do this and that's to flip the blend around, so the secondary source is only returning the latest date for the project, and the primary source is looking at all the dates. This will get rid of the Null value in the Date field filter, and mostly get rid of the Null value in the Downgrade/Upgrade filter:

This uses the blended source to get the Latest week's status, and since the blend is only returning a single value we don't need the Date field in the view. This simplifies the rest of the calcs and we can compute the Downgrade/Upgrade as a regular aggregate. However, Tableau won't let us put discrete aggregate measures on the Filters shelf, so I wrapped that in a LOOKUP() formula to create a table calc filter.

I set both of these up in the attached workbook.

If you wanted the user to dynamically choose both dates, there's yet another route using a multiple select filter that could be used.

Great explanation and work. Thanks a bunch. I was stress testing it and figured out if a project id is

present in a latest date and has a status ( lets say red) and not present in an earlier date - in this case when you select the earlier date for comparing the statuses ( I know I would be comparing Null to Red ) that project does not shows up. That project could be a new entry in an actual data for the latest date. Basically irrespective of the comparison date, I would like all project ids to show up.

Is there any way to resolve this? Would I have to do joins between the two sources instead of blending?

In the data blending solutions I offered, when you select an earlier date that doesn't exist for a given project as a record in the data source, what happens is you select the filter, then in the data blending process Tableau only retains tuples (combinations of project and date) that exist in both data sources.

If you always want all project ids to show up *and* show current data no matter what is filtered for, the workarounds that I can think of are:

1) Use a parameter for selecting the date instead of a data source. This would require updating the parameter every week. Andy Kriebel demo'ed a hack for downloading, updating parameters, and re-uploading a workbook at #data14.

2) Use a custom query or view to pad out your data so there's a record for every project/date combination in the data for the date range that you are using, this would likely be using some sort of cross-product join, and then use one of the data blending solutions I posted. Without knowing your data volumes I can't say whether that would work, however it would most precisely match the experience for users that you want.

3) Use a custom query or view to left-join this week's data with every prior week that exists, so if there are no prior weeks then the prior week fields would all have Null values. Then all the calculations could be record-level calculations, and you could potentially use Tableau's "Show Empty Rows/Columns" feature to retain the display, or you'd have to train your users to use a multi-select filter and keep Null selected while they changed other options.