Display background color in Pivot table on selecting a year

[#1]

I am using pivot table to display the data.  There are three years 2016 (current year),2015(Year-1),2014,(year-2) in the application.

When i select 2016, I have to display 2016,2015,2014

                     2015 -  2015,2014

                     2014 - 2014

I use a condition in the background color in each expression and in the dimension - year field.  When i select 2016, the background color for columns corresponding to 2014 and 2015 disappears. When i select 2015, the background color in 2014 disappears.

I want to show the color always when i select the year. 2016  - max(year), 2015 - max(year)-1, 2014 - max(year)-2

Before selecting the year

After selecting the year 2016

[#2]

anil can you explain your expressions?

[#3]

@pooja:  i have attached the qvw.  can you please check it?

[Anil_Babu_Samineni]

Sorry for delay, I am doing some other work. I've gone thru your application.Can you check this

Dimensions are

Month & Year as usual

Expression is

Sum({<year = {"$(='<=' & Max(year))"}>}value)

BG for Expression

If(Sum({<year = {"$(=Max(year))"}>}value),RGB(248,237,236),

If(Sum({<year = {"$(=Max(year)-1)"}>}value),RGB(226,242,246),

If(Sum({<year = {"$(=Max(year)-2)"}>}value),RGB(240,238,228))))

2016 Images seems this

2015 Images seems this

And, finally Enclosed attached

[#5]

Thanks Anil.  I am going to check it and I will tell you. For columns with zero values, we can use isnull(sum(...)), rgb()?

[#6]

its not working.  You changed the expression.  there is no annual data for 2016(only Q1),  Annual(2014  and 2015).  I am not using three expressions for each year.

My expression is

=if(year=$(= max(year)), sum({<quarter={'q1'},   year={"$(=max(Année))"}>}   value), sum({<year={">=$(=max(year)-2)<=$(=max(year)-1)"}>}value))

I used quarter field for 2016.  How to use the above expression in background color?

I tried different possibilities, nothing is working.

[Anil_Babu_Samineni]

NP, We will help you

In your data model there is no Quarter field, Would you share latest Application to work on it same

[#8]

Please check the attached qvw.

[Anil_Babu_Samineni]

I am out of thoughts, Meantime i recommend them to help

stalwar1‌ gwassenaar‌ Marco Wedel 

And Others also can help{ Gain is no limit }

Ajay - You may put Mark as helpful from Actions. Then, Others can link those quickly

[sunny_talwar · Accepted solution]

Is this what you want?

When 2016 is selected

When 2015 is selected

Background expression I used is this:

=if(Only({<year>} year)=(max(TOTAL {<year>} year)-2), blue(),

if(Only({<year>} year)=(max(TOTAL {<year>} year)-1), Green(),

if(Only({<year>} year)=max(TOTAL {<year>} year), red())))

[#11]

@sunnyT : Thank you so much.  its working now.