1 2 Previous Next 17 Replies Latest reply on Dec 15, 2014 9:30 AM by Jonathan Drummey

# Comparing  value of a field in two different dates

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

The base week is always the current week.

Can anyone help me accomplishing this?

• ###### 1. Re: Comparing  value of a field in two different dates

Attach workbook, it will help us to Understand.

Best Regards

Laxman Kumar

• ###### 2. Re: Comparing  value of a field in two different dates

Hi Laxman

I attached a workbook to explain better. Thanks

• ###### 3. Re: Comparing  value of a field in two different dates

Hi we can create calculation like below

If [Status this week]= "Red" and [Status last week]="Yellow" then "Downgrade"

elseif [Status this week]= "Green" and [Status last week]="Yellow" then "Upgrade"

elseif [Status this week]= "Green" and [Status last week]="Red" then "Upgrade"

end

Before above calculation,

convert your dated into Weeks & define Current Week & Last Week.

datepart("Week", today()) ---- Current Week

datepart("Week", today()) -1   ---- Previous Week

Best Regards

Laxman Kumar

• ###### 4. Re: Comparing  value of a field in two different dates

Hi Laxman

Thanks for your response but how do I create the [Status this Week] and [Status Last Week] columns. I don't have that in my data source.

Thanks

• ###### 5. Re: Comparing  value of a field in two different dates

Hoping to get an answer for this from experts !!!! Mean while trying from my side  ...

• ###### 6. Re: Comparing  value of a field in two different dates

I hope it will work for you.

if    datepart("Week",[startdate]) = datepart("Week", today()) then [Status] end

 if datepart("Week",[enddate]) = datepart("Week", today()) then [Status] end
• ###### 7. Re: Comparing  value of a field in two different dates

Hi,

Have worked through the solution ... Hope it Helps ...

Thanks,

Suhrid Ghosh

2 of 2 people found this helpful
• ###### 8. Re: Comparing  value of a field in two different dates

Hi Suhrid

Do I need to have the latest Tableau version to open the above file?

• ###### 9. Re: Comparing  value of a field in two different dates

Unfortunately Yes ... Since i am on 8.2.2 ...

• ###### 10. Re: Comparing  value of a field in two different dates

Hi Suhrid

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

Thanks

• ###### 11. Re: Comparing  value of a field in two different dates

Need help from the experts ... calling Matt Lutton ... Jim Wahl ... Jonathan Drummey

• ###### 12. Re: Comparing  value of a field in two different dates

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.

9) Created a "Selected Status (non-optimized calc)" using the formula

IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

MIN([Status])

END

(Steps 4-9 draw from Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers).

8) Put that on Rows, you'll now have a view that looks like this:

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.

Here's the Latest Status calc:

IF LAST()==0 THEN

WINDOW_MAX(ATTR(IF [Is Latest Date] THEN [Status] END))

END

And the Selected Status calc:

IF LAST()==0 THEN

WINDOW_MAX(IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

MIN([Status])

END)

END

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:

//only test for last address in partition (latest date)

IF LAST()==0 THEN

//checking in case the user selected the latest date

IF ATTR([Date field]) == ATTR([Sheet2 (Sample worksheet) (copy)].[Date field]) THEN

"Selected date is latest"

ELSE

IF [Latest Status] == "Red" AND ([Selected Status] == "Yellow" OR [Selected Status] == "Green") THEN

ELSEIF [Latest Status] == "Yellow" AND [Selected Status] == "Green" THEN

ELSEIF [Latest Status] == "Green" AND ([Selected Status] == "Yellow" OR [Selected Status] == "Red") THEN

ELSEIF [Latest Status] == "Yellow" AND [Selected Status] == "Red" THEN

END

END

END

In the view this also has a Compute Using on the Date field:

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.

Jonathan

1 of 1 people found this helpful
• ###### 13. Re: Comparing  value of a field in two different dates

Thanks Jonathan

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?

• ###### 14. Re: Comparing  value of a field in two different dates

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.

Jonathan

1 2 Previous Next