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

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
sunny_talwar

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

View solution in original post

12 Replies
Anonymous
Not applicable
Author

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
Partner - Creator III
Partner - Creator III

Hi Shashank,

Could you write your expected output here as a excel.

Regards,

Vishnuram Jayaraman.

Anonymous
Not applicable
Author

From this data, its look like.

Awaiting for Shashank confirmation

swuehl
MVP
MVP

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
Author

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

This gives blank values Swuehl

sunny_talwar

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

swuehl
MVP
MVP

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