Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

1 Solution

Accepted Solutions
MVP
MVP

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

Capture.PNG

12 Replies
balrajahlawat
Esteemed Contributor

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

MVP
MVP

Re: Sum of Running Difference

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

jvishnuram
Contributor III

Re: Sum of Running Difference

Hi Shashank,

Could you write your expected output here as a excel.

Regards,

Vishnuram Jayaraman.

balrajahlawat
Esteemed Contributor

Re: Sum of Running Difference

From this data, its look like.

Awaiting for Shashank confirmation

MVP
MVP

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)

Not applicable

Re: Sum of Running Difference

Result
0
7
5
5
6
10
And finally Sum(Result)
Not applicable

Re: Sum of Running Difference

This gives blank values Swuehl

MVP
MVP

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

Capture.PNG

MVP
MVP

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