# App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
Partner

## above() function and date selection

Hi,

Consider the table  presented below.

 Data Load Quantity Category 31/10/2018 74.170 apple 31/10/2018 3.264.618 orange 31/10/2018 7.487.132 pear 30/11/2018 79.892 apple 30/11/2018 3.329.629 orange 30/11/2018 7.318.647 pear 31/12/2018 85.071 apple 31/12/2018 3.382.092 orange 31/12/2018 7.162.199 pear 31/01/2019 91.648 apple 31/01/2019 2.515.989 orange 31/01/2019 7.923.292 pear 28/02/2019 95.574 apple 28/02/2019 3.459.038 orange 28/02/2019 6.862.316 pear 31/03/2019 99.109 apple 31/03/2019 3.499.973 orange 31/03/2019 6.753.454 pear

Starting from the selection of the dates of the year 2019 (e.g.) I need to calculate the difference between the sum of the "quantity" of a date and the sum of the "quantity" of the date  immediately above. The second table display the expected result.

 Data Load Difference Quantity (data n) - (data n-1) 31/01/2019 -98433 28/02/2019 -114001 31/03/2019 -64392

I've tried to solve using different formulas based on the function ABOVE without success.  The issue is related with the selection applied on the dimension Data Load, indeed selecting the dates of the year 2019 the  function ABOVE doesn't  consider the not selected dates computing the difference and in particular the last date "excluded" in the selection (see the attachment).

=(sum( [Quantity])  - Above(sum( [Quantity])))

Even trying to using set analysis aimed to ignore the selection of the data load dimension didn't reach  the desired result.

Does someone have  any suggestion?

thanks

Davide

Labels (4)

• ### Set Analysis

1 Solution

Accepted Solutions
MVP

Try this

=RangeSum(
Sum([Quantity]),
) *
Avg(1)

8 Replies
MVP

Try this

=RangeSum(
Sum([Quantity]),
) *
Avg(1)

Partner
Author

Thanks Sunny,

I didn't understand completely why, but works!

I've improved the formula in case of selection of other temporal dimension.

=RangeSum(
Sum({<[Category]=-{'apple'}>}[Quantity]),
) *
Avg(1)

MVP

Superb... the only other thing I would change is to add the set analysis from your main expression to the Avg(1) expression

=RangeSum(
Sum({<[Category]=-{'apple'}>}[Quantity]),
) *
Avg({<[Category]=-{'apple'}>} 1)
Partner
Author

thanks again.

I didn't understand the contribute  of the "Avg" function, may I ask you an explanation?

and why should work better adding the condition

Avg({<[Category]=-{'apple'}>} 1)

thx

Davide

MVP

Basically, by adding the set analysis to ignore selection in Data Load, Year, Month fields you are telling your expression to show everything regardless of selection in those fields.... now how do you force non selected items to go away? For this we use Avg(1).... Avg(1) = 1 for selection and Avg(1) = 0 for non selections....

Asking to add the same set analysis to Avg... because you want Avg() to work for the same selections where it works for your main expression....

Does it make sense?

Partner
Author

yes is smart, I've understood decomposing the formula.

Just another question regarding the set analysis. I've been used to  consider group operator like the following

Sum({<[Category]={'Apple'}>}[quantity]))

in your formula I've noticed this syntax (e.g.):

Sum({<[Category]>}[quantity]))

In this case, what is the function of the operator without the = ?

Thanks

Davide

MVP

That just means that I am ignoring selection in Category field... Similar to {<[Category] = >}

Creator III

How can we use this expression in a KPI object or Text Object?

Tags
Community Browser