topic Re: above() function and date selection in App Development

above() function and date selection

davdiste — Sat, 16 Nov 2024 05:05:45 GMT

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

Re: above() function and date selection

sunny_talwar — Thu, 01 Aug 2019 12:11:35 GMT

Try this

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

Re: above() function and date selection

davdiste — Thu, 01 Aug 2019 14:04:40 GMT

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)

Re: above() function and date selection

sunny_talwar — Thu, 01 Aug 2019 14:25:24 GMT

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)

Re: above() function and date selection

davdiste — Thu, 01 Aug 2019 14:33:40 GMT

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

Re: above() function and date selection

sunny_talwar — Thu, 01 Aug 2019 16:01:24 GMT

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?

Re: above() function and date selection

davdiste — Fri, 02 Aug 2019 09:45:34 GMT

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

Re: above() function and date selection

sunny_talwar — Fri, 02 Aug 2019 16:42:35 GMT

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

Re: above() function and date selection

Rehan — Sun, 22 Mar 2020 03:25:18 GMT

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