Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Masters
I have the table below (Pivot)
The dimension "Region" is a calculated dimension:
=if(Mother_Vertical='Direct',Sub_Vertical)
"2019" formula:
sum({<Master_Month=,Master_Quarter=,Master_Week=>}Spread_over_COF_$)/sum({<Master_Month=,Master_Quarter=,Master_Week=>}R_ANI)
Region | TM Name | 2019 |
Rio de Janeiro | Ederson | 2.24% |
Rio de Janeiro | Weverton | 2.51% |
Minas Gerais | Fabio | 2.49% |
Minas Gerais | Dani Alves | 1.73% |
Minas Gerais | Thiago Silva | 2.89% |
Sao Paulo | Miranda | 2.36% |
Sao Paulo | Filipe Luís | 2.84% |
Sao Paulo | Marquinhos | 2.75% |
Sao Paulo | Danilo | 2.48% |
Sao Paulo | Alex Sandro | 2.38% |
Sao Paulo | Éder Militão | 1.90% |
Upon adding 2018 (below), the rows in blue appear (as those names existed in 2018)
"2018" formula:
Sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}Spread_over_COF_$)/sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}R_ANI)
However, I would like to NOT have those rows as I want to disclose only names that exist in 2019
Region | TM Name | 2019 | 2018 |
Rio de Janeiro | Ederson | 2.24% | 2.13% |
Rio de Janeiro | Fabio | - | 1.78% |
Rio de Janeiro | Ramirez | - | 1.70% |
Rio de Janeiro | Weverton | 2.51% | 2.50% |
Minas Gerais | Fabio | 2.49% | 1.63% |
Minas Gerais | Dani Alves | 1.73% | 1.74% |
Minas Gerais | Thiago Silva | 2.89% | - |
Minas Gerais | Alex | - | 3.27% |
Sao Paulo | Miranda | 2.36% | 1.98% |
Sao Paulo | Filipe Luís | 2.84% | 2.86% |
Sao Paulo | Marquinhos | 2.75% | 2.54% |
Sao Paulo | Danilo | 2.48% | 1.95% |
Sao Paulo | Alex Sandro | 2.38% | 1.97% |
Sao Paulo | Éder Militão | 1.90% | 1.84% |
How can I solve this issue?
Thanks for the help
Thanks Rubenmarin,
I was able to fix it with this:
Sum({1<Master_Year = {$(=$(vMaxYear)-1),"TM Name"=P({1<Master_Year={$(=$(vMaxYear)}>} "TM Name")}>}Spread_over_COF_$)
/
sum({1<Master_Year = {$(=$(vMaxYear)-1),"TM Name"=P({1<Master_Year={$(=$(vMaxYear)}>} "TM Name")}>}R_ANI)
Thanks Rubenmarin,
I was able to fix it with this:
Sum({1<Master_Year = {$(=$(vMaxYear)-1),"TM Name"=P({1<Master_Year={$(=$(vMaxYear)}>} "TM Name")}>}Spread_over_COF_$)
/
sum({1<Master_Year = {$(=$(vMaxYear)-1),"TM Name"=P({1<Master_Year={$(=$(vMaxYear)}>} "TM Name")}>}R_ANI)