Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
=RangeSum(
Sum([Quantity]),
-Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)
Try this
=RangeSum(
Sum([Quantity]),
-Above(Sum({<[Data Load]>}[Quantity]))
) *
Avg(1)
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)
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)
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
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?
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
That just means that I am ignoring selection in Category field... Similar to {<[Category] = >}
How can we use this expression in a KPI object or Text Object?