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.


      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?






      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



            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



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

                  Then [Property Value] End) / 4 * 300



                  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:



                        {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.