Showing variances between multiple records

[#1]

Hello,

I am looking to analyse data relating to weight loss.

I have been able to use the first sorted value expression to find the most recent record by the set dimensions.

FirstSortedValue([Measurement measurementValue], -num([Measurement actionDate]))

I can also identify the oldest record by FirstSortedValue([Measurement measurementValue], num([Measurement actionDate]))
I need to be able to show all records in between, (there could be hundreds of records between the most recent and the oldest), and the variances between each one.

Is there a similar aggregration command to do this?

Is there a more efficent way in set analysis of carrying this out also?

Thanks for any assistance.

C

[#2]

Hey Cayfmatt,

If i understood correctly you want to find the variance between say Row 1 and row2, row2 and row3 and so on ? (Sorted by Date)

Example:

RowNo	Date	Value	Variance
1	2/1/2013	10	-
2	2/10/2013	32	22
3	2/15/2014	45	13
4	2/22/2014	3	-42
5	2/28/2014	23	20

Is this correct?

Thanks

AJ

[#3]

Thank you, Ajay. Yes, thats what I am looking for although the data is sorted in descending order on date

[#4]

I have attached a simple example on how to do this. I have used Previous() function to accomplish this.

Hope it helps.

Thanks

AJ

[#5]

Thanks Ajay but I am unable to open the qvw file. The previous function sounds useful. Could you please paste the necessary on here?

[#6]

Sure. Please copy & paste this code and run it. Put all the fields in a table box on front-end and sort by date desc and you will see the variance values as expected.

Table:

LOAD * INLINE [

    RowNo, Date, Value

    1, 1/1/2013, 23

    2, 2/4/2013, 45

    3, 4/23/2013, 6

    4, 10/2/2013, 87

    5, 1/1/2014, 3

    6, 2/24/2014, 10

];

Test:

NoConcatenate

Load *,Value-Previous(Value) as Variance

Resident Table

Order by Date desc;

Drop Table Table;

[maxgro · Accepted solution]

or,

in chart, use the above (below) function

your expression shoul be

Sum(Value) - above(Sum (Value))

above([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

On the first row of a column segment a NULL value will be returned, as there is no row above this one.

[#8]

Thanks Ajay and Massimo.

Massimo - I adapted your expression and this seems to be working. This has showed another query as well

where I have 2 values on the same date, the chart is combining them.

so

For example

Date           Value

01/03/14      100

02/03/14        200

02/03/14       200

03/03/14     150

The value is showing 400 for 02/03/14 when I wish it to display each record separately.
Is there an equivalent of distinct I can use in the script?