Sum of Running Difference

[#1]

My Data looks something like this

Id	Name	Value
1	30A	12
2	40A	19
3	50A	24
4	5f	29
5	4g	35
6	4h	45

Now I want a measure which will be the sum of all the Differences that is

(19-12)+(24-19)+(29-24)+(35-29)+(45-35)
I am a newbie with Qlik please help in writing a query which helps solve this requirement

[Anonymous]

Write a new Expression like:

=below(Value) - Value

check this as well:

Missing Manual - Above() and Below()

Above & below Function Expressions List

[sunny_talwar]

Isn't the above sum equivalent to 45-12?

[jvishnuram]

Hi Shashank,

Could you write your expected output here as a excel.

Regards,

Vishnuram Jayaraman.

[Anonymous]

From this data, its look like.

Awaiting for Shashank confirmation

[swuehl]

I think Sunny is right, the sum of the differences should be last - first value.

For example, in a striaght table chart with dimension Id you could use an expression like

=Bottom( Only(Value) ) - Top( Only(Value) )

or in a text box maybe

=Only({<Id = {$(=Max(Id))} >} Value) - Only({<Id = {$(=Min(Id))} >} Value)

[#7]

Result

0

7

5

5

6

10

And finally Sum(Result)

[#8]

This gives blank values Swuehl

[sunny_talwar · Accepted solution]

Try this Expression:

=Alt((Value - Above(TOTAL Value)), 0)

With Total Mode as 'Sum' of Rows

or

=Sum(Aggr(Alt((Value - Above(TOTAL Value)), 0), Id, Name, Value)) with Expression Total

[swuehl]

Both of my expressions works for me. Please define the context in which you want to use the expression, context is always important.

If you are using a straight table chart with more than one dimension, you may need to add the TOTAL qualifier:

=Bottom(TOTAL Only(Value) ) - Top(TOTAL Only( Value) )

If you want to see the sum of running difference on each row:

= Only(Value) - Top(TOTAL Only(Value))