8 Replies Latest reply on Jun 16, 2018 5:08 PM by Jalal Hassan

# [HELP] Percent Difference W/o Separate Worksheet.

Hi,

So I have been pulling my hair over this issue.

I have a Date Parameter Calculation that allows users to compare period. The issue I am facing is that I need to introduce the % Difference between the two periods.

While I do have a workaround in place it is not ideal.

I've attached sample of how my data is structured, using the superstore data. It will be the last sheet.

My issue is that while the superstore data only has 4 regions, I have 30+ regions I need to compare so scrolling is necessary, so it makes it difficult displaying the % difference for each region.

Ideally I would like to find a way to display it all in a single sheet, the period comparisons and the % difference.

Period Calculation

```IF
DATETRUNC([Period Length],[Book Date])=
IIF([Period Comparison]=1,1,
IIF([Period Comparison]=2 AND [Period Length]="day",365,
IIF([Period Comparison]=2 AND [Period Length]="week",52,
IIF([Period Comparison]=2 AND [Period Length]="month",12,
IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
AND
DATETRUNC('day',[Book Date])<=
IIF([Period Comparison]=1,1,
IIF([Period Comparison]=2 AND [Period Length]="day",365,
IIF([Period Comparison]=2 AND [Period Length]="week",52,
IIF([Period Comparison]=2 AND [Period Length]="month",12,
IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))

THEN

"Period Comparison"

ELSEIF
(DATETRUNC([Period Length], [Book Date]) =
DATETRUNC([Period Length], [Period End Day])
AND
DATETRUNC('day', [Book Date]) <=
DATETRUNC('day', [Period End Day]))

THEN

"Current Period"

END
```

There is also a Period End, Period Comparison and Period Length Parameter.

Basically for my workaround I am hijacking the Grand Total column in a seperate worksheet and allowing the user to click on the region to show the % Difference.

• ###### 1. Re: [HELP] Percent Difference W/o Separate Worksheet.

HI Jalal

I used "Category" as placeholder for three "period".  (Used "category" because it also has three values)

Used LOD to calculate value based on region nevertheless of "Category"

"%" and "Previous and Current" should be two different fields because of format difference.

Placehoplders

Such a simple view, it required 100 steps!! - Having Multiple KPIs - - Still Struggling with Excel ?? <Tableau's Room>

Educational Brain Teaser ==> Answer : Colorful Conditional Format

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: [HELP] Percent Difference W/o Separate Worksheet.

Shin,

I will dig into this once I get back to the office tomorrow.

The larger question is why Tableau just does not allow % Difference in the Grand Total....

• ###### 3. Re: [HELP] Percent Difference W/o Separate Worksheet.

Hi Jalal

G. total's logic is different in Tableau's query, but I don't quite understand what you are struggling.

Could you clarify that as wel?

Shin

• ###### 4. Re: [HELP] Percent Difference W/o Separate Worksheet.

Shin,

I am having trouble grasping the concepts presented here.

I am familiar with some of these methods, but relatively new to Tableau. (Company transition to it 3 Months ago)

I don't believe the above method will work with how my data needs to be structured.

I've attached a screenshot of what the Dashboard the end user views looks like.

The issue with how I currently am doing it is it does not all ow the end user to sort by what regions declined/grew the most percentage wise period over period.

Sorry if I seem to be missing something incredibly obvious.

Regards,

Jalal Hassan

• ###### 5. Re: [HELP] Percent Difference W/o Separate Worksheet.

HI Jalal

Not quite sure what sorting order you are seeking, but for example, it's easy to sort region by % diff Sales.

Shin

• ###### 6. Re: [HELP] Percent Difference W/o Separate Worksheet.

Shin,

The method you are showing me, while it does work when using a limited number of variable. Like the two in your example.

It becomes unwieldy  to use for an end user, if there is a multitude of variables that they need to  view/sort in different ways.

Thanks for your help, but I will continue looking at different methods.

• ###### 7. Re: [HELP] Percent Difference W/o Separate Worksheet.

HI Jalal,

If you have many measure, you had better pivot data to get dimension for each measure value.

Because your sample does not allow me to use Pivot option, I did not show that approach.

Actually, changing data source requires all formula's re-creation which I hesitated to do.

Thanks,.

Shin

• ###### 8. Re: [HELP] Percent Difference W/o Separate Worksheet.

Shin,

After spending a week playing around. I have come up with a solution and put it in play.

I will go ahead and post the solution I came up with later tomorrow. No time to do it right now.

Regards,

Jalal Hassan