5 Replies Latest reply on May 9, 2018 7:29 PM by swaroop.gantela

    Heatmap - Calculation on previous values

    anabel.fernndez.0

      Hello,

       

      I am trying to do a Heatmap (weekdays/hour of day) to check for increases in the counters depending on day/hour.

       

      The data is structured as follows:

      Location Index: It is an index to indicate the sensor collecting the data

      Date: the date of the record.

      Input: The counter for that sensor

       

      The records are not distributed equally between time (they are saved when an external event occur), indeed one counter could be a couple of weeks turned off and reporting no data.

      Counters are dependant on its location index; each sensor has its own counter.

       

      Main problem is related with substracting Input value in the timeframe with the previous value for that location index.

       

      My try:

      • I define a Date_trunc_hours field: { INCLUDE [Location Index]:Max(datetrunc('hour',[Date]))}
      • Then I create a calculated field like this: { INCLUDE [Location Index]: max( { Exclude [date]: max(if [date]<[Date_Trunc_hours] then [Input] end) }
      • The problem with this is that since [Date] is excluded, it seems to be getting the MAX value in the whole dataset.

       

      I am kinda stuck with this, so any help or guidance will be greatly appreciate.

       

      You can find attached a packaged workbook with a small subset of data included. Tableau version 10.4.4.

       

      Thank you very much.

       

      Best regards,

        • 1. Re: Heatmap - Calculation on previous values
          swaroop.gantela

          Anabel,

           

          Please see if the workbook attached in the Forum thread could be a first step.

          I don't think it's what you're looking for, because I wasn't sure how you were going to aggregate things.

           

          In the sheet entitled "Full Table", the idea is as you described: group by location and date-hour, find the max date,

          get it's corresponding value, and then do the difference between those values.

           

          It first finds the Max Date for a given hour (done by location) as [MaxDatePerHour]:

          { FIXED [Location Index],[Date (Hours)]:MAX([Date])}

           

          Then gets that InputValue as [MaxDateInputValue]:

          { FIXED [Location Index],[Date (Hours)]:MAX(

          IF [Date]=[MaxDatePerHour] THEN [Input] END)}

           

          So the difference would be:

          MAX([MaxDateInputValue])-LOOKUP(MAX([MaxDateInputValue]),-1)

          which needed a "Compute Using" of all Specific Dimensions at the Deepest Level, restarting every Location Index.

           

          I think these are the values that you would like to plot.

          However, I wasn't sure how you wanted to handle the situation as with

          Location-1/Saturday/7am where both May 5 and May 12 have values.

          I thought of averaging them, but it seemed like one would want to view them separately.

          I also wondered if you wanted to view just one location at a time, or if you wanted to see

          all the locations on one view.

           

          So the sheet "heatmap-ish" has a square for each location-weekday-hour combination,

          colored by InputValueDifference. (Please note: the Table Calculation was different on this sheet

          as compared to the settings for the "Full Table")

           

           

          269573heatmap.png

          • 2. Re: Heatmap - Calculation on previous values
            anabel.fernndez.0

            Yeah, sorry I didn't explain myself well.

             

            I would like to aggregate the data in a SUM way (sum of all location counters -difference counters- and all dates).

             

            You can think on the data as a system to count people entering an exhibit. They can enter through several gates (Location Index) and the Input value is the all-life counter of people that has entered through that gate. For the heatmap I would like to have a view with the sum of all people entering the exhibit depending on hour/weekday (I really don't mind which gate they have used).

             

            I have prepared another example with what I want to achieve. For this example I have supposed that I have another column in the data source (Diff_Input):

            Location Index: It is an index to indicate the sensor collecting the data

            Date: the date of the record.

            Input: The counter for that sensor

            Diff_Input: The difference between updated input value and previous value of inputtableau.PNG

            (Lunes is Monday, Martes is Tuesday, Sábado is Saturday and Domingo is Sunday)

             

            For Saturday from 00:00 to 00:59 we have the following data:

              

            LocationIndexDateInputDiff_Input
            105/05/2018 0:25100000
            105/05/2018 0:4610194194
            405/05/2018 0:58125470
            1005/05/2018 0:59700000

             

            So we get a value of 194

             

            For Monday from 00:00 to 00:59:

              

            LocationIndexDateInputDiff_Input
            1107/05/2018 0:1890491160
            1014/05/2018 0:3672865182

             

            And we get 342 (the sum of all Diff_Input independently of which LocationIndex).

             

             

            Maybe the easiest way to go would be to just create another Database/table with the Diff_Input column but I rather prefer to use a Tableau-only solution if possible.

             

            Thank you very much.

            • 3. Re: Heatmap - Calculation on previous values
              swaroop.gantela

              Anabel,

               

              Thank you for the further detail and explanation.

              The attached isn't quite there yet, the numbers aren't right, but it gives the idea.

              I need to still work on the correct summing across Location Index.

              • 4. Re: Heatmap - Calculation on previous values
                anabel.fernndez.0

                Hello Swaroop,

                 

                Sorry for not answering before but I have been very busy today.

                 

                It looks very nice!! It seems that once multiple Location Indexes are Summed it will be perfect

                 

                I will try to have a deeper look tomorrow.

                 

                If you have any update about summing Location Indexes please let me know.

                 

                Thank you very much!!

                • 5. Re: Heatmap - Calculation on previous values
                  swaroop.gantela

                  Anabel,

                   

                  Hmm.

                  I think I'm able to get a table of differences organized in the grid of hour vs. weekday.

                  However, this is the disaggregated individual amounts. It's proving trick to sum them up.

                   

                  What I might recommend would be first to see if my calculated differences are correct.

                  I made an attempt to crosscheck using Dashboard1 of the attached.

                   

                  If those differences are correct, then I would suggest reposting that workbook

                  as a new question and see if someone in the community is able to get it sum properly.

                   

                  A whole other avenue to explore would be to try and use Tableau Prep for the aggregation.

                   

                  Apologies.