6 Replies Latest reply on Jul 20, 2016 11:28 AM by David Li

    Problems with isnull and previous_value()

    JamesC

      Hi,

       

      We are struggling with a simple line chart in which there is no data for weekends, for this reason we check for null values and if there is a null value we take the Previous_Value

      IFNULL(AVG([Spot_Raw]),PREVIOUS_VALUE(1))
      

       

      This on itself is working, but we also have  another calculated field to highlight the last 9 days of data:

      IF DATEDIFF('day',ATTR([Reported Timestamp]),NOW())<9 THEN [Spot] END
      

       

      When we add this second field to the chart, the previous values stop working (going to 1). Any suggestion why this might be happening?

       

      Workbook attached

       

      Thanks

       

      J

       

      Message was edited by: James Cox

        • 1. Re: Problems with isnull and previous_value()
          David Li

          Hi James, a couple things first:

          1. Did you extract your data source before saving the packaged workbook? If not, please do that, package, and then upload the new version.
          2. You probably don't want 1 as the argument for PREVIOUS_VALUE(), because that's actually the number that PREVIOUS_VALUE will return if it can't find that calculation's value in the previous cell.
          • 2. Re: Problems with isnull and previous_value()
            JamesC

            Hi David,

             

            I have re-uploaded the document.

            We could use a different default value for PREVIOUS_VALUE(), but in effect the query should work, as we had it working before with a slightly different data source.

            It is interesting that it only fails when the _LastWeek part is added (I guess it is related to the fact that _LastWeek has mostly null values for anything other than the last 9 days from today.

             

            Thanks

            • 3. Re: Problems with isnull and previous_value()
              David Li

              Hi James, you can get rid of that weird issue by changing ATTR([Reported Timestamp]) to MIN([Reported Timestamp]). For some reason, using ATTR() there causes the fill-in for missing values to behave differently. However, this still causes a break in the LastWeek line for the weekend. Working on that part now.

              • 4. Re: Problems with isnull and previous_value()
                David Li

                Okay, I finally got it. It's a bit more complicated than I wish it could be, but basically, I had to use a running sum to determine when the prior week starts. I couldn't get it to work otherwise, but I'm not exactly sure why.

                 

                RUNNING_SUM(IIF(DATEDIFF('day', MIN([Reported Timestamp]), TODAY()) < 9, 1, 0))
                

                 

                IIF([LastWeek Filter] > 1, [Spot], NULL)
                

                 

                If this has been helpful, please do mark it as the correct answer so it can help others!

                • 5. Re: Problems with isnull and previous_value()
                  JamesC

                  Thanks David, that works perfectly.

                   

                  I'm still confused why the other method does not work properly, if it is either a bug or a intended functionality, it seems to suggest the order of the calculation is not kept when both were fields were being charted.

                   

                  Very impressed and thankful at how fast you spotted it!

                   

                  All best

                  J

                  • 6. Re: Problems with isnull and previous_value()
                    David Li

                    Hi James, you're very welcome, and I'm glad I was able to solve the issue!

                     

                    I'm also confused about why the other method doesn't work. It has something to do with the missing values filler, but I still don't know why sidestepping that issue with lookups didn't work. Oh, well!