6 Replies Latest reply on Nov 20, 2017 9:18 AM by Rick A

    Yet Another Most Recent Value Question (LOD Calc)

    Rick A

      Good afternoon.  I feel like I should have solved this myself, but after hitting close to a dozen posts for reference I couldn't get what I wanted on my end.  Apologies in advance if this turns out to be overly easy.

       

      I'm attempting to use a calculated value to show a variable reference point that is calculated using the PhysicalMemory value.  An example of the database rows of interest are below:

         

      Display NameRecord TimePropertyProperty Value
      Location111/13/2017 15:08PhysicalMemory4
      Location111/14/2017 14:14PhysicalMemory4
      Location111/15/2017 11:59PhysicalMemory12
      Location111/15/2017 14:32PhysicalMemory12
      Location111/15/2017 14:52PhysicalMemory8
      Location111/15/2017 16:05PhysicalMemory8

       

      With some assistance from another source I previously worked out the following calculation:

       

      PLEThreshold = {FIXED [Display Name],[Record Time]: Max(If [Property] = "PhysicalMemory" Then [Property Value] End) / 4 * 300}

       

      This performed well in cases like above where the PhysicalMemory went from 4 to 12.  My reference line would go from 300 to 900 as expected.  Where it fails me is if the PhysicalMemory is adjusted down.  The expected value of my calculated field would be 600, but continues to display as 900.

       

      The worksheet this applies to appears below.  The detail mark MAX(PLEThreshold) contains the calculation above.

      Capture.PNG

      If someone doesn't mind I would appreciate some education on the result of the FIXED calculation above.  If I change MAX(PLEThreshold) to SUM(PLEThreshold) I get a different (larger - obviously some addition happened) value.  Shouldn't that calculation spit out only a single value?

       

      Thanks.

       

      Rick

       

      Edit:  Added example workbook/data.  Jim Dehner & Deepak Rai.

        • 1. Re: Yet Another Most Recent Value Question (LOD Calc)
          Deepak Rai

          please attach a  workbook

          • 2. Re: Yet Another Most Recent Value Question (LOD Calc)
            Jim Dehner

            Hi Rick

             

            I would really like to see a workbook to do this but first let translate what the LOD expression is saying -

             

            The way Fixed forks is that it creates permutations of the dimensions that precede the colon then aggregates them by what follows -

            so in your case it would read

                      For each combination of of Display Name and Record

                      take the Max

                      of either the Property Value (only if if the Property = Physical Memory) and then divide that by 4 and multiply by 400

             

            so now you have a new set of values that represent all the permutations of Display Name and Record - some of those records have a value (i.e. all those with Property = Physical Memory) all the rest have a Null value - Note this new data set is NOT aggregated - that is you can use it in other calculations or you can bring it to the viz with some form of aggregation - sum(), avg(), max(), Min() and others

             

            also and very important - if you bring it to the viz and only display name is in the viz it will aggregate across all the records for each display name

            and of course other filters apply - you have to decide how to aggregate the PLEThreshold

             

            Lets say for Display Name x there were 12 Records so you have 12 values - do you want the AVG() of the 12 or the Max() of the 12 - etx

             

            Also note you have a lot of Nulls so if you pull the expression and there are null records you have to zn() , ifnull or isnull them out of the calculation

             

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Yet Another Most Recent Value Question (LOD Calc)
              Rick A

              I think I found a solution in using a nested FIXED to get my max date:

               

              {FIXED [Display Name],[Record Time]: Max(

                  If [Property] = "PhysicalMemory"

                  AND [Record Time] = {FIXED:MAX([Record Time])}

                  Then [Property Value]

                  End) / 4 * 300

              }

               

              That allowed me to compare dates in the If statement where MAX([Record Time]) wasn't allowed.  I'll have to run it through some tests tomorrow to be sure, but it gave me the expected result for what I was looking at today.

               

              Should that be cleaned up in any way to follow best practices?

              • 4. Re: Yet Another Most Recent Value Question (LOD Calc)
                Rick A

                Well that was a bust.  The calculation worked with the test data because the max record time was the same for every display name.  Live data won't be so consistent.

                • 5. Re: Yet Another Most Recent Value Question (LOD Calc)
                  Shawn Wallwork

                  Try:

                   

                  (If [Property] = "PhysicalMemory"  AND [Record Time] = {FIXED [Display Name] : MAX([Record Time])}

                  Then [Property Value] End) / 4 * 300

                   

                  --Shawn

                  1 of 1 people found this helpful
                  • 6. Re: Yet Another Most Recent Value Question (LOD Calc)
                    Rick A

                    Using your calc led me to making a change to the last one I posted:

                     

                    INT(

                        {FIXED [Display Name]: Max(

                            If [Property] = "PhysicalMemory"

                            AND [Record Time] = {FIXED [Display Name]:MAX([Record Time])}

                            Then [Property Value]

                            End) / 4 * 300

                        }

                    )

                     

                    Removing [Record Time], if I understand how this works correctly, means grouping all the display names together resulting in a single max entry.  Having [Record Time] still in there sliced the data up more than I wanted resulting in multiple returns per display name.

                     

                    I appear to be good to go now.  Thanks for the response.