Need help with making dynamic selections with First sorted value

Mon, 11 Jun 2012 09:48:26 GMT

Good Morning, I am having problems with mentioned expression, I am using the expression below to get most recent meter read and meter read a year ago.

FirstSortedValue(Meter_Reading,-Meter_Reading_Date,1)

FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)

This works on the assumption if there are 2 reads a year, this becomes wrong if more than one read is done a year (which happens a lot)

Also I have put 2 restrictions in the script to only load Meter reading type value of either 01 or 02 and meter reading reason being 01 or 02

The diagram below shows how the data extracted when using first sorted value using my method becomes skewed, this happens if above values don't fall in the 01 or 02 category.

The latest read shows a read of 230 when using FirstSortedValue(Meter_Reading,-Meter_Reading_Date,1)

and 0 for FirstSortedValue(Meter_Reading,-Meter_Reading_Date,3)

I understand why this is the case as it has ignored any rows with data that dont fit withing script restrictions and in this example I am only wanting the data that fits in the data for date 18.04.2012 and 18.10.2012. This will often change and have a number of reads between which may have a variety of values that dont fit above restrictions, this is making things very difficult

I am ideally looking for the latest read and a read a year back from that date that have the 01 or 02 restrictions, ive been pulling y hair out for weeks to get this to work, help would be much appreciated!

Paul.

Need help with making dynamic selections with First sorted value

Mon, 11 Jun 2012 11:53:42 GMT

Anyone?

topic Need help with making dynamic selections with First sorted value in QlikView

Need help with making dynamic selections with First sorted value

Need help with making dynamic selections with First sorted value