topic Returning values within a set range in QlikView

Returning values within a set range

Mon, 28 May 2012 15:14:22 GMT

Good afternoon, I need to do some averages on some data but I only want to use the most recent date and a date thats as close to as a year ago as possible. i.e.

28.05.2012

24.02.2012

10.10.2011

08.07.2012

29.05.2012

26.05.2012

Above is a range of dates using the most recent date and a date up to a year ago(as close as possible) ignoring all the data inbetween.

How do I go about this?

Kind regards.

Paul.

Returning values within a set range

Mon, 28 May 2012 15:37:01 GMT

Hi, in the scrip you can make a table to load top 10 o 20 dates, the data source table must be ordered by date.

Returning values within a set range

Tue, 29 May 2012 08:00:02 GMT

Ermm that really didnt help me at all. Im needing help on scripting, not on ordering the data that I already have, I am needing to get the 1st and last date value within a range of a year from today.

Paul

Returning values within a set range

flipside — Tue, 29 May 2012 13:40:25 GMT

Hi Paul,

You could add a flag field to your load script setting dates within past year to 1 and others to 0, then specify this flag in a set analysis statement for your averages. That's how I normally do running YTD calculations.

flipside

Returning values within a set range

Tue, 29 May 2012 14:04:02 GMT

ooo! Have you got any examples of this please, im fairly new to set analysis

Returning values within a set range

Tue, 29 May 2012 14:06:47 GMT

at the moment im using

if(max(Meter_Reading_Date)<=today(),Meter_Reading)

(as an expression)

its giving me randomly correct and incorrect and even sometimes blank data

Returning values within a set range

Tue, 29 May 2012 15:27:06 GMT

I've gotten FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,-Meter_Reading_Date) to work out the latest meter read date.

How do I go about getting a meter reading thats the furthest back inside a year?

Re: Returning values within a set range

flipside — Wed, 30 May 2012 10:09:54 GMT

Here is something that should help. I wasn't sure if you wanted the oldest date within the year, or the date nearest to 1 year ago, so have added flags in the load script for both options.

Re: Returning values within a set range

Wed, 30 May 2012 13:53:15 GMT

This looks very good, a little complex for what im trying to achieve. I currently use this to obtain the most recent date.

FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,-Meter_Reading_Date)

I use this to get the oldest date:-

FirstSortedValue(Places_Before_Decimal_Point_In_Meter_Reading,Meter_Reading_Date).

im needing an adaptation of this to get the oldest value up to 425 days of age. Do you now how to achieve this ?

Re: Returning values within a set range

flipside — Wed, 30 May 2012 22:43:01 GMT

I would probably use variables to make this easier.

1) Set up a variable to hold the value of the comparison date (Latest date minus 425)

vDateCompare

Definition: =max(Meter_Reading_Date)-425

2) Use this expression

=date(alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date)),'DD/MM/YYYY')

This can be broken down into 3 parts as follows ...

i) Return the latest date where the date is less than the variable

=max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date)

ii) If there are no dates, you need to return the oldest date available so wrap an alt statement around it

=alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date))

iii) Finally, if this is to go into a text box, format the expression to a date

=date(alt(max({<Meter_Reading_Date={'<$(vDateCompare)'}>} Meter_Reading_Date),min(Meter_Reading_Date)),'DD/MM/YYYY')

flipside

Re: Returning values within a set range

Thu, 31 May 2012 12:09:42 GMT

Thanks for your help. This has been a good learning experience, Luckily I got to the result I needed by correctly applying First sorted Value.