5 Replies Latest reply on Oct 12, 2018 8:24 AM by Joe Oppelt

    Previous Day Lookup?

    Chad Clauson

      Hi, I'm using a single day dashboard filter and I have a chart that uses that filter and compares a field from the day selected with the same field from the previous day. While the value from the day selected is right, my previous day value is not correct. Please see attached for sample workbook.

       

      Sheet 1 is currently what it looks like (prev day # incorrect), while Sheet 2 shows you the data between the two days. Each row on sheet one should give the error code, the previous day count for that error code, the current day (single date dashboard filter) count for that error code, and the percent change for those.

       

      You can see from sheet 2 that the previous count is instead taking the number from the row above on the same day rather than going to the day before and checking the specific error code count, so there is an issue with my lookup formula.

        • 1. Re: Previous Day Lookup?
          Joe Oppelt

          See attached.

           

          On Sheet 3 I put your PREV calc, and I set the table calc settings to cycle through [Error code] and then [Date], and to restart every [Error Code].  (you don't want the last value of "10T" to be the lookup value for "AGR", for instance.)

           

          You see the results on Sheet 3.

           

          Go to Sheet 3(2).

           

          Here I put a table calc filter to select the date to display.  When you use a table calc as a filter, it doesn't delete rows from the underlying table.  It just controls what part of the table gets displayed.  So Oct 1 is still in the table for the LOOKUP -1 to find it.

          • 2. Re: Previous Day Lookup?
            Chad Clauson

            So from your Sheet 3 (2) example, would I have to replace my dashboard date filter with that if I wanted to go by date on all my views? I just have a few snapshot bar charts that I don't want to mess up.

             

            Also your second solution partially works for my actual table. The issue is that the previous day may not have that error code and it will use the previous value from another date. For example, let's say I have a 1OT error on 9/25/18 with a count of 5, then the 1OT error doesn't show up in my table again until 10/2/18 with a count of 7. The previous day value will say 5 when it should really be null (or 0 if I use zn in my case). Any solution for that issue? Thanks for your help!

            • 3. Re: Previous Day Lookup?
              Joe Oppelt

              Chad Clauson wrote:

               

              So from your Sheet 3 (2) example, would I have to replace my dashboard date filter with that if I wanted to go by date on all my views? I just have a few snapshot bar charts that I don't want to mess up.

               

               

              If you want to control multiple sheets with one filter, unfortunately we can't do "apply to" with a table calc filter.

               

              So in that case you would give them a parameter to select a date.  Then you would make your filter (a separate one on each sheet, but each would be identical) that looks like this:

               

              LOOKUP( ( IF ATTR([Date]) = [Pick a date] then 1 else 0 END), 0)

               

              Use that on the filter shelf, and select for value = 1.  (I did this on Sheet 3 (2) for you.)

               

              Chad Clauson wrote:

               

              ...

               

              Also your second solution partially works for my actual table. The issue is that the previous day may not have that error code and it will use the previous value from another date. For example, let's say I have a 1OT error on 9/25/18 with a count of 5, then the 1OT error doesn't show up in my table again until 10/2/18 with a count of 7. The previous day value will say 5 when it should really be null (or 0 if I use zn in my case). Any solution for that issue? Thanks for your help!

              Also on Sheet 3(2) I created another LOOKUP to grab the date of the -1 cell.  I displayed that on the sheet.  You don't have data to prove it out here, so you'll have to test it on your fuller data.  When you know it's right, then you can add that to your previous day calc.  (I did that in your calc in the attached.)  Notice now that instead of null in the first row, we now get a zero because of how I set up the conditions in the calc.

              1 of 1 people found this helpful
              • 4. Re: Previous Day Lookup?
                Chad Clauson

                I tried using the formulas you created and for some reason the Filter Date calculated field isn't working for me. When I use it as a filter, it is only giving me an option of 0. I wish I could show you my actual data. The rest of the calculated fields seem to work. Opening a new sheet and using ATTR(Date) for a specific error code gives me a " * " so that may be the issue.

                 

                Regardless, I'm marking your answer correct as you've helped me this far so thank you.

                • 5. Re: Previous Day Lookup?
                  Joe Oppelt

                  Chad Clauson wrote:

                   

                  ... When I use it as a filter, it is only giving me an option of 0. I wish I could show you my actual data. ...

                  You can anonymize a subset of your data:

                   

                   

                   

                  Video demonstrates how to anonymize your workbook/data

                   

                  Likely it's something in the table calc settings.