Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Showing variances between multiple records

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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.

View solution in original post

7 Replies
Not applicable
Author

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:

RowNoDateValueVariance
12/1/201310   -
22/10/20133222
32/15/20144513
42/22/20143-42
52/28/20142320

Is this correct?

Thanks

AJ

Not applicable
Author

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

Not applicable
Author

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

Hope it helps.

Thanks

AJ

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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?