Show increase in all rows using RangeAvg (Above(sum(amount,4))

[fran_perea]

Hello there,

Im trying to show the increase in a table. I have the data in quarters and want to compare same quarter with previous year. But the first 4 in the table doesn´t show the increase. 

The set analysis formula I´m using is this one:

((sum( {<[operation] = {'Sales'},Date= {">=$(=Date(min(Date)))<=$(=Date(max(Date)))"},Year,Quarter>} Amount)
-
sum( {<[operation] = {'Returns'},Date= {">=$(=Date(min(Date)))<=$(=Date(max(Date)))"},Year,Quarter>} Amount))

/

RangeAvg (Above(sum( {<[operation] = {'Sales'},Date = {">=$(=Date( addyears(min(Date),-1) ))<=$(=Date( addyears(max(Date),-1) ))"},Year,Quarter>} Amount)
-
sum( {<[operation] = {'Returns'},Date = {">=$(=Date( addyears(min(Date),-1) ))<=$(=Date( addyears(max(Date),-1) ))"},Year,Quarter>} Amount),4))

-1

Is there a way to show the increase in all the rows of the table? 

EDIT:

I show all the data. I understand i can´t know the increase in 2019 due to lack of data to compare. But in the first image 2284 being 2021_Q1 should be able to compare to 2020_Q1. 

Thanks

[marcus_sommer · Accepted solution]

A bypassing of the native behaviour of the interrecord-functions like above() could become quite complex because they are accessing available values from the chart and the previous ones to the first row just didn't exists.

This means you will need an artificial extending of the dimensionally scope of the object and depending on further dimensions + selections and other requirements some on-top filtering to remove unwanted dimension-values again which might be enforced by the scope-extending.

To keep it simple I suggest you don't calculate the rate at first else just summing one of the base-kpi. This means as the first expression:

sum({< [operation] = {'Sales'}>} [Value])

and as the second expression something like:

rangesum(above(sum({< Quarter >} aggr(sum({< [operation] = {'Sales'}, Quarter>} [Amount]), Quarter))))

The main-idea behind it is to use an aggr() to extend the the selection-scope by ignoring some selections within the inner and/or outer aggregation. Quite probably will you need to play a bit with it. Therefore try to avoid each kind of extra complexity like more selection, set analysis or more dimensions - just to return the previous value. If this worked you may extend it step by step.

An alternatively would be not to use interrecord-functions and removing at least one Year-Quarter information as dimensional value and then using appropriate expressions. This might be done by using only Quarter as dimension and querying the Year in the expressions within set analysis like:

sum({< [operation] = {'Sales'}, Year = {"$(=max(Year)-2)"}>} [Value])
sum({< [operation] = {'Sales'}, Year = {"$(=max(Year)-1)"}>} [Value])
sum({< [operation] = {'Sales'}, Year = {"$(=max(Year)-0)"}>} [Value])

Such a view will be different to your example but you will be able to show all essential information.

[fran_perea]

Thanks!

I couldn´t make this work, but gave me the idea to make a meassure in the load script to add a number every quarter and use that in your formula. NOw is working.