Show only names that exists in the current year (Pivot table)

[felipe_oliveira]

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

[rubenmarin]

Hi, you can check value of 2019 column before calculating 2018, ie:
If(not Isnull([2019]), Sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}Spread_over_COF_$)/sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}R_ANI))

If '2019' is a calculated name, apply the same expression:
If(not Isnull([$(=NameExpression)]), ...

[sivakumar1994]

try this for 2018 expression:

if( not isnull([2019])
Sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}Spread_over_COF_$)/sum({1<Master_Year = {$(=$(vMaxYear)-1)}>}R_ANI),0)

[felipe_oliveira · Accepted solution]

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)