above() function and date selection

[davdiste]

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

[sunny_talwar · Accepted solution]

Try this

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

 

[davdiste]

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)

[sunny_talwar]

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)

[davdiste]

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

[sunny_talwar]

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?

[davdiste]

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

[sunny_talwar]

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

[Rehan]

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