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

# Yet Another Most Recent Value Question (LOD Calc)

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 Name Record Time Property Property Value Location1 11/13/2017 15:08 PhysicalMemory 4 Location1 11/14/2017 14:14 PhysicalMemory 4 Location1 11/15/2017 11:59 PhysicalMemory 12 Location1 11/15/2017 14:32 PhysicalMemory 12 Location1 11/15/2017 14:52 PhysicalMemory 8 Location1 11/15/2017 16:05 PhysicalMemory 8

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?

Thanks.

Rick

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

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

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 -

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)

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)

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)

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)

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.