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

    Create Table with Data with Different Granularity

    Alberto Izarraraz

      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.

       

      test dashboard.PNG

        • 1. Re: Create Table with Data with Different Granularity
          swaroop.gantela

          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.

           

          Please see workbook v10.5 attached in the Forum Thread:

          https://community.tableau.com/thread/285892

           

          285892calc.png

          • 2. Re: Create Table with Data with Different Granularity
            Alberto Izarraraz

            Thanks. Hmm.. so i think that's how it might work with window_max. I couldn't figure it out.

             

            I did figure out how to solve the issue eventually. I learned about context filter. So i basically grabbed the max for that measure but then put the filter into context. This will adjust the data before it calculates the max instead of getting the max of all the data first.