2 Replies Latest reply on Oct 29, 2018 6:39 AM by Alberto Izarraraz

# Create Table with Data with Different Granularity

Hello All,

I've been having an issue with a table I've been trying to create within Tableau. I'm hoping that anyone might know how to do this. So thank you for taking a look at it already.

Here it is:

I want to create this type of dashboard for the user. This will allow them to select each of the months that they want to look at and one of the years. However, the measure "Test Ytd" is a precalculated measure from my client the rest of the them are calculations.

So here is the issue. In the filters if they choose January through August 2018 "Test Ytd" should display the value of August 2018 and not an average or sum or anything else. But for First Value it needs to sum up Jan to August 2018 and for Second Value it should average up to that Jan to August 2018. If it is up to September then "Test YTD" should display September 2018 value only.

Test Ytd should be a % less than 100%. Please let me know if that made any sense. I attached the workbook for clarification on it.

My attempts have been to make a calculated field  for Test Ytd with a max date but then it doesn't get the max date of what is filtered but of the whole data set.

• ###### 1. Re: Create Table with Data with Different Granularity

Alberto,

I'm not sure if I caught the gist, but maybe the below

can give some ideas.

I think it's going to take a few steps, mostly requiring Table Calculations.

For the table calculations to work, both [Date] and [Month] will need to be on

the detail shelf. (This will make many copies of the value, but will

take a max and then filter it down to one copy).

Running sum of First Value:

WINDOW_MAX(RUNNING_SUM(SUM([First Value])))

Average of Second Value:

WINDOW_AVG(SUM([Second Value]))

Ytd of only max month:

WINDOW_MAX(

IF ATTR([Month])=WINDOW_MAX(MAX([Month]))

THEN SUM([Test Ytd])

END)

These will all require Table Calculation settings as shown below.

Lastly, as this created many copies, we will put INDEX() on the Filter shelf,

put table calculation settings same as below, then filter down to just one.