Dynamic Output of growth with table calcs

[Nadirfb]

I would like to calculate a table calculation that shows something like it follows:

Subject	2017 Revenue	Difference Previous	Growth?	2018 Revenue	Difference Previous	Growth?	2019 Revenue	Difference Previous	Growth?	2020 Revenue	Difference Previous	Growth?	Output
Music	10	null	null	12	2	yes	13	1	yes	16	3	yes	3 years growth
Art	10	null	null	12	2	yes	11	-1	no	13	2	yes	no growth
Sports	10	null	null	9	-1	no	12	3	yes	13	1	yes	2 years growth

So year is a pivot obtained from created_date

revenue is a sum dimension

Growth and difference previous are table calculations:

Growth:

case(
  when(${difference_previous}>0,"Y"),
    when(${difference_previous}<0,"N"),
    "No info")

Difference Previous:
${test_revenue.tot_revenue} - pivot_offset(${test_revenue.tot_revenue},-1)

Do you know how can I create a table calculation for this output?

The conditions are:

If Growth = yes at least in the last two years, output should be “X years growth” being X the number of consecutive yes

If Growth = yes only in the last year ot growth = no in the last year oyput should be “No growth”

Thanks in advance

[Nadirfb]

Edited:

I have created 4 table calculations to achieve this:

Years #To calculate the number of years and use it as index

diff_years(pivot_index(${test_revenue.created_revenue_year},1),trunc_years(now()))



Difference Previous #Basically the difference between this year and the previous year in the pivot table

${test_revenue.tot_revenue} - pivot_offset(${test_revenue.tot_revenue},-1)




Growth Bool #To calculate if there is growth compared with the previous year
case(

  when(${difference_previous}>0,"YES"),
    when(${difference_previous}<0,"NO"),
    "")



Output #To calculate the last 3 years growth with nested ifs

if(
  pivot_index(${growth_bool},(${years}))="YES", 
    if(pivot_index(${growth_bool},${years}-1)="YES",
      if(pivot_index(${growth_bool},${years}-2)="YES",
      "3 years growth","2 years growth"),
    "Last year Growth"), "No Growth Last year"
)

So now it looks like:

Subject	2017 Revenue	Difference Previous	Growth?	2018 Revenue	Difference Previous	Growth?	2019 Revenue	Difference Previous	Growth?	2020 Revenue	Difference Previous	Growth?	Output
Music	10	null	null	12	2	yes	13	1	yes	16	3	yes	3 years growth
Art	10	null	null	12	2	yes	11	-1	no	13	2	yes	no growth
Sports	10	null	null	9	-1	no	12	3	yes	13	1	yes	2 years growth

But if I want to calculate not only for the last 3 years but for all the years in the date frame I don’t know how to achieve it.

Do you know how to create something like a loop? for i=years,i--;i=0?

Thanks again