7 Replies Latest reply on Nov 17, 2015 11:26 AM by Rody Zakovich

# Calculating percentage difference compared to previous week number

I'm trying to calculate the percentage of difference of number of records compared to the previous week. Doesn't have to be rolling, as I'm trying to find it for the difference between today's week compared to last.

So I've got my date field in the Columns as WEEK(Registration Timestamp) and the SUM(Number of Records) in the Rows. So the line graph right now shows the # of records with a Registration Timestamp of that week.

How do I get the %age difference from the previous week? I've followed this tutorial, but can't get it working at the week number level:Percent Difference From Calculation

When I go to add in a table calculation, I make the calculation type as 'Percent Difference From', Calculate the difference along: 'Registration Timestamp', At the level 'Day of Registration Timestamp' and the Display the value as a percent difference from: 'Previous'

So my first question is: why is there no 'week' option for the parameter "At the level"?

Second, how do I do this for the absolute date, not all the 1st of each month?

• ###### 1. Re: Calculating percentage difference compared to previous week number

Brian,

I have not quite understood the question, but try something.

File attached created by version 9.0.

Thanks,

Shin

Change to week bucket. Then change the start day of week.  ==> follow  the link.

http://kb.tableau.com/articles/howto/monday-first-day-of-week

absolute Date: • ###### 2. Re: Calculating percentage difference compared to previous week number

Awesome, now I know what I was doing wrong - it was trying to enforce the week rule at the table calculation level as well.

So if I want to display just this week's percentage difference compared to last week's number, how do I do that? I tried filtering the date to show just this week, but because the filter doesn't show the previous week's # of records, it isn't able to calculate the percentage difference. Any idea how to show just this week's percentage difference?

• ###### 3. Re: Calculating percentage difference compared to previous week number

Little bit tough in that case.

However, two ways I can propose.

Anyways, you need to create parameter added from [date].

Then calc field like below.

[Show date]

if datepart('week',[Parameter 1])=datepart('week',[Date])

or datepart('week',([Parameter 1]-7))=datepart('week',[Date])

then [Date]  end

Put [Show date] instead of [date] then exclude null.

Your table calc still works  in this case.  (==> see one way tab)

Another way is also create three more calc field

[Parameter week count]

if datepart('week',[Parameter 1])=datepart('week',[Date]) then 1 else  0 END

[Parameter Previous week count]

if datepart('week',([Parameter 1]-7))=datepart('week',[Date]) then 1 else  0 END

[Current week count / Previous week count]

sum([Parameter week count])/sum([Parameter Previous week count])

Then you can show in one line.   (==> Another way tab)

Thanks,

Shin

• ###### 4. Re: Calculating percentage difference compared to previous week number

Hey Brian,

For your second question you could do

IF LAST() == 0

THEN SUM([Value]) / LOOKUP(SUM([Value]), -1)

END

And set that to compute using your Date.

LAST() == 0 equates to the most recent Date in your Viz, because the most recent Date will be the LAST Partition.

So on the most recent date, Take the SUM of that Value and Divide it be the SUM of the Value from the previous partition (Previous Week). Since we don;t add an ELSE statement, all partitions that aren't the LAST one will equate to NULL, aka, no value will show.

Regards,

Rody

• ###### 5. Re: Calculating percentage difference compared to previous week number

Hey Rody, thanks for the suggestion. Is there a way to use that calculation as a filter? I originally followed this post (Re: Can I calculate a shape as an 'up' or 'down' arrow depending on a quick table calculation?), but I think I got it with your tip.

Basically, I put your calculation as the filter. But since this week's value calculates to 7, the filter is set to that number. Will this automatically update next week? How do I filter out everything except for NULL? Or will the current filter work in the future as well?

• ###### 6. Re: Calculating percentage difference compared to previous week number

Nevermind, found the tab within filters to do it

• ###### 7. Re: Calculating percentage difference compared to previous week number

Hey Brian,

Sorry for the late response.

Did you get your issue solved?

If so can you please mark a response a correct, or share your solution and mark that as correct.

This will help other members who are searching for answers for similar questions.

Regards,

Rody