Published on InterSystems Developer Community (https://community.intersystems.com)

Home > Retrieving the absolute last entry from set of members in a level

Question
Steve Pisani · Oct 22, 2017

Retrieving the absolute last entry from set of members in a level

Hi,

I have a persistent class “sp.SensorReading” which has a number of Properties: Date, SensorName, SensorReading.   (sometimes multiple readings from the same sensor, on a given day)

...
Temp    28 Jan                    33.5
Temp    29 Jan                    31.2
Temp    30 Jan                    33.1
Temp    30 Jan                    34.1
Temp    31 Jan                    32.1
Temp    31 Jan                    33.1

Say I built a cube built to analyse data  from this persistent class.  With a simple date dimension, and SensoRName filter, I can show the default COUNT of readings for a given month/year:

Jan-2015             94
Feb-2015             54
Mar-2015             78
…

 

I have a date dimension with levels for Year, Quarter, MonthYear, and Date. Instead of the default COUNT measure , I want to get the last value pushed out by a sensor for a given selected period. So let’s say, the last value for temperature, for each date dimension member: This is what I would want to see if selecting on MonthYear:

Jan-2015              33.1
Feb-2015             32.2
Mar-2015             31.8
…

My approach is be to define a calculated measure which, returns the reading from the last member of the selected group of readings, using the MDX %LAST function.

Something like the following defined in the cube - Calculated Measure: LastReading:

%LAST([SensorDate].Members,Measures.SensorReading)

 

However – As in the example for January above, sometimes, I get multiple readings on the same day for the same sensor, and if that happens to be the last day of the month, the reading are added.

Jan-2015            65.2
Feb-2015             32.2
Mar-2015             31.8

Is it possible to have a pure MDX - based expression to fix this, ie, when %LAST(...Members) actually returns multiple entries ?

(ie, Can I achieve what I want without introducing a cube dimension that is resolved based on whether a particular reading is the last reading of the month, at data upload or build – because – the period is not always defined as ‘month’, but could be Year, YearQuarter, or even arbitrary dates.

thanks - 

#Analytics #Analyzer #InterSystems IRIS BI (DeepSee)

Source URL:https://community.intersystems.com/post/retrieving-absolute-last-entry-set-members-level