Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

above() function and date selection

Hi,

Consider the table  presented below.

Data LoadQuantityCategory
31/10/201874.170apple
31/10/20183.264.618orange
31/10/20187.487.132pear
30/11/201879.892apple
30/11/20183.329.629orange
30/11/20187.318.647pear
31/12/201885.071apple
31/12/20183.382.092orange
31/12/20187.162.199pear
31/01/201991.648apple
31/01/20192.515.989orange
31/01/20197.923.292pear
28/02/201995.574apple
28/02/20193.459.038orange
28/02/20196.862.316pear
31/03/201999.109apple
31/03/20193.499.973orange
31/03/20196.753.454pear

 

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

 

 

 

 

 

1 Solution

Accepted Solutions
Highlighted

Re: above() function and date selection

Try this

=RangeSum(
     Sum([Quantity]),
    -Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)

image.png 

View solution in original post

8 Replies
Highlighted

Re: above() function and date selection

Try this

=RangeSum(
     Sum([Quantity]),
    -Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)

image.png 

View solution in original post

Highlighted
Partner
Partner

Re: above() function and date selection

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]),
-Above(Sum({<[Data Load],[Year],[Month],[Category]=-{'apple'}>}[Quantity]))
) *
Avg(1)

 

Highlighted

Re: above() function and date selection

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]),
-Above(Sum({<[Data Load],[Year],[Month],[Category]=-{'apple'}>}[Quantity]))
) *
Avg({<[Category]=-{'apple'}>} 1)
Highlighted
Partner
Partner

Re: above() function and date selection

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

Highlighted

Re: above() function and date selection

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?

Highlighted
Partner
Partner

Re: above() function and date selection

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

 

Highlighted

Re: above() function and date selection

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

Highlighted
Creator II
Creator II

Re: above() function and date selection

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