4 Replies Latest reply on Aug 2, 2018 8:03 AM by susheela.hegde

# How to find the Average ignoring Week End data also how to hide the parameter

Hi All,

I have two questions in my workbook.

First Question:

I want to display Actual Vs Average Value when we select the Date Parameter and the period.

How ever, the average should be excluding the Week end Values.

For example in attached workbook, when We select 5/7/2017 i.e. sunday, we need to display actual value  for 5/7 and  average will be excluding sunday and saturday.

What ever date we select, Average value should be excluding weekends and actual will be displayed all the time.

When We display the numbers in view, Order date will not be displayed in the view (need to have LOD INCLUDE).

Similarly , when we select Period as week or month , it should display actual and then average value for month or week(excluding weekend).

I am unable to get all of these together.

Second Question:

I have two parameters . Date Selection and Select Period.

When We select Period, Yesterday, Date Selection Parameter should be hidden. When Select Period Parameter <> 'yesterday' then show Date selection Parameter.

Can we do this in tableau?

Any Help is Appreciated!

• ###### 1. Re: How to find the Average ignoring Week End data also how to hide the parameter

Susheela,

I'm not sure if this quite got there, but please if the below can give ideas.

This method used Table Calculations. You can likely adapt it LODs

[Weekend Boolean]:

DATENAME('weekday',[Order Date])="Saturday"

OR

DATENAME('weekday',[Order Date])="Sunday"

[Nonweekend Quantity]:

IF NOT [Weekend Boolean] THEN [Quantity] END

[Selected Dates Quantity]:

IF [Select Period]="day" AND ATTR([Order Date])=[Date Selection]
THEN SUM([Quantity])
ELSEIF [Select Period]="week" AND DATEPART('week',ATTR([Order Date]))=DATEPART('week',[Date Selection])
THEN SUM([Quantity])
ELSEIF [Select Period]="month" AND MONTH(ATTR([Order Date]))=MONTH([Date Selection])
THEN SUM([Quantity])

// etc. for other parameter selections
END

[Selected Total]:

WINDOW_SUM([Selected Dates Quantity])

Repeat the above two calculations for the NonWeekend version,

using WINDOW_AVG for the aggregate.

The final view filters out the duplicate copies using an Index Filter.

With regards to the parameter hiding, you maybe could add

Sheet selector - how to put sheets in same position?

• ###### 2. Re: How to find the Average ignoring Week End data also how to hide the parameter

Swaroop,

Sheet Selector will not work because i want to hide Date Parameter based on the value in another parameter .

Even If I put parameter in one sheet and select it, parameter doesn't hide .

correct me if I am wrong with this.

• ###### 3. Re: How to find the Average ignoring Week End data also how to hide the parameter

Susheela,

That may be the case.

Best to search through the content of Joe Oppelt

who has done extensive work on sheet popping and swapping.

Though not your situation, here is an example, :

Show/Hide Sheet Based on Calculated Field ?

• ###### 4. Re: How to find the Average ignoring Week End data also how to hide the parameter

Thanks Swaroop.

But this does not work for me . I have date Parameter automatic and not any derived field from the data.  I do see we can show parameter if it is a list and have them in one sheet. But my end user want to see Date Parameter and automatic.