6 Replies Latest reply on Dec 6, 2016 1:02 AM by Bobo Wieland

    Fill "holes" in dataset

    Bobo Wieland

      I connect to a dataset that have values for each 'day', and also values for 'week' every sunday and 'month' the last day of every month:

       

      ...
      ('2016-07-28', 'day', 100),

      ('2016-07-29', 'day', 105),

      ('2016-07-30', 'day', 110),

      ('2016-07-31', 'day', 100),

      ('2016-07-31', 'week', 790),

      ('2016-07-31', 'month', 35012),

      ('2016-08-01', 'day', 98),

      ('2016-08-02', 'day', 100),

      ('2016-08-03', 'day', 103),

      ('2016-08-04', 'day', 101),

      ('2016-08-05', 'day', 95),

      ('2016-08-06', 'day', 93),

      ('2016-08-07', 'day', 70),

      ('2016-08-07', 'week', 650),

      ('2016-08-08', 'day', 81),

      ...

       

      How can I go about getting values for week and month for every day using this dataset? Would like interpolated data so I can present these values as continous Area Charts.

        • 1. Re: Fill "holes" in dataset
          Sherzodbek Ibragimov

          Bobo,

          Lets try those steps:

          1) Right click Date and choose Custom Split

           

          2) Now you have 3 more slip fields and rename them:

           

          3) Do some trimming and formatting as well as excluding monthly, weekly data which you can customize using different calculated field later on:

          Note: When you do custom slip, those new fields will have their own calculation, but I have changed them little bit to make it usable as the date and amount. So, please make sure you have the same calculation as below

          4) Now we can built Area Chart based on new fields:

           

          I have attached workbook for reference. Hope it helps.

           

          Sherzod

          • 2. Re: Fill "holes" in dataset
            Sherzodbek Ibragimov

            Bobo,

            It has been almost two weeks since your question was answered, I just wanted to check back with you if solution provided here answered your question or not. If yes, please mark as correct answer or as assumed answer if you find your solution so that it is cleared from question board that needs to be answered as well as other users in future can benefit from it. If not, please feel free to ask for help. Thank you.

            Sherzod

            • 3. Re: Fill "holes" in dataset
              Bobo Wieland

              Had to leave this project behind for a while. In doing so I forgot to follow up. I will check and get back to you soon.

              • 4. Re: Fill "holes" in dataset
                Bobo Wieland

                This is not what I was after. Probably my fault for being unclear when writing the question. First of; I have de data in Tableau already. No need for custom splits. For me it was just a convinient way to display the dataset.

                 

                What I would like is a value for 'week' and 'month' for every date in the dataset. Not only for the dates with actual data. So, I can think of two different ways that the data should be presented when done: For example: say we're looking at 'month' which have a value of 10000 for '2016-08-31' and a value of 10300 for '2016-09-30' then I would wither like a value of '10300' for every day in the span '2016-09-01' to '2016-09-30' or use an interpolated value with '10010' for '2016-09-01', '10020' for '2016-09-02' ... '10290' for '2016-09-29' and '10300' for '2016-09-30'

                • 5. Re: Fill "holes" in dataset
                  Bobo Wieland

                  I'm kind of desperate here. I'll paste an image to illustrate my problem(s).

                   

                  tableautrouble.png

                   

                  The data set only include these values. But I need to have monthly and weekly for every day.

                   

                  Problem 1: For the 1st to the second to last day of the month the value should be the same as for the last day of the month. For monday to friday the value should be the same as for the upcomming sunday.

                  Problem 2: If you look at the highlighted values. The values for weeklyActive should be ignored. The data set has the value since they are needed for caclulating the monthlyActive some how. But I should not use it in my work.

                   

                  One more example. I tried to caclulate weeklyActive / monthlyActive. My thought was to have the AVG och the last 5 weeklyActive through monthlyActive for the last month. Did not work since now Tableau thinks that the missing values are a bunch of zeroes.

                  • 6. Re: Fill "holes" in dataset
                    Bobo Wieland

                    So, I managed to fix this, finally. Simple fix that I should have thought of before.

                     

                    I had access to the data source and could filter and blend the data. So I connect to the same data source three times and joins every 'day' once on month for 'month' values and once on week for 'week' values.