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

    Heatmap - Calculation on previous values




      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



          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:


          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")




          • 2. Re: Heatmap - Calculation on previous values

            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:


            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:


            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



              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

                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




                  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.