12 Replies Latest reply: Dec 29, 2015 5:43 AM by Sunny Talwar

Sum of Running Difference

My Data looks something like this

IdNameValue
130A12
240A19
350A24
45f29
54g35
64h45

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

• Re: Sum of Running Difference

Write a new Expression like:

=below(Value) - Value

check this as well:

Missing Manual - Above() and Below()

Above & below Function Expressions List

• Re: Sum of Running Difference

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

• Re: Sum of Running Difference

From this data, its look like.

Awaiting for Shashank confirmation

• Re: Sum of Running Difference

Hi Shashank,

Could you write your expected output here as a excel.

Regards,

Vishnuram Jayaraman.

• Re: Sum of Running Difference

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)

• Re: Sum of Running Difference

This gives blank values Swuehl

• Re: Sum of Running Difference

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

• Re: Sum of Running Difference
Result
0
7
5
5
6
10
And finally Sum(Result)
• Re: Sum of Running Difference

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

• Re: Sum of Running Difference

Hi Sunny, Anyway that I can Filter out just values that are like 'OA' in the above expression. When I filter out that would give me (19-12)+(24-19)

• Re: Sum of Running Difference

Is that 'OA' or '0A'? If its '0A' then you can try this expression:

=Sum(Aggr(Alt((Only({<Name = {'*0A'}>}Value) - Above(TOTAL Only({<Name = {'*0A'}>}Value))), 0), Id, Name, Value)) with Expression Total

• Re: Sum of Running Difference

Hi shashank,

use this expression,

Aggr((Sum(Value)-Above(Sum(Value))),Name)