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
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