topic Re: Display background color in Pivot table on selecting a year in QlikView

Display background color in Pivot table on selecting a year

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Thu, 08 Dec 2016 01:15:48 GMT

Would you provide sample to work on it same as you

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 01:29:13 GMT

here is my code

In expression _ background color - =if(year=(max(year)-2), $(v_Color.year_2),if(Année=(max(year)-1) $(v_Color.year_1) ,if(Année=max(year),$(v_Color.current_year))))

should i create one expression for each year and write the color directly in background property?

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Thu, 08 Dec 2016 01:44:07 GMT

What are the Year and Année

May be this for your expression

I assume, You have the variable with v_Color

=if(year=(max(year)-2), $(v_Color),if(year=(max(year)-1), $(v_Color) ,if(year=max(year),$(v_Color))))

If(GetFieldSelections(year) = Max(year), $(v_Color))

Instead of that i worked same for you check this

Script:

LOAD * Inline [

Year, Sales

2014, 10

2014, 20

2014, 30

2015, 10

2015, 15

2016, 30

2016, 40

];

And I've create one Straight table

Dim -- Year

Expression --- Sum({<Year = {"$(='<=' & Max(Year))"}>}Sales)

For that Expression, I've used this Back Ground color

If(GetFieldSelections(Year) = Max(Year), Red())

For that Expression, I've used this Text color

If(GetFieldSelections(Year) = Max(Year), White())

Images are looking this

For 2014 --

For 2015 --

For 2016 --

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 06:20:23 GMT

Thanks for the reply Anil. Year and Annee are same. When you select 2016 in your straight table, I want to see background color for 2015 and 2014.

When you select 2015, background color should be displayed for 2014 also. The target is when you select a year, display the background color. In my application, the color disappears because its not dynamique.

In my application, I use two expression. One for year 2016(have only Q1 data) and second expression for 2015(annual) and 2014(annual).

if(year=$(=max(year)), num( (count(distinct {<Month={'march'},quarter={'q1'},year={$(=max(year))},"year-month"=,[Contrat - Type Code]-={'ST'},EndofMonth={1},[Period]={'quarter'}>} Matricule)),

num( (count(distinct {<Month={'dec.'},year={">=$(=max(year)-2)<=$(=max(year)-1)"},"year-month"=,EndofMonth={1},[Contrat - Type Code]-={'ST'},[Period]={'Annual'}>}Matricule)) ,'# ##0',',',' ')

)

when i select 2015, no background color for 2014.

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Thu, 08 Dec 2016 06:29:24 GMT

Like this?

For 2016 Only --

For 2015 Only --

Re: Display background color in Pivot table on selecting a year

Anonymous — Thu, 08 Dec 2016 06:32:32 GMT

Hi Ajay,

Can u share ur QVW so will be easy for us to implement this

Thanks,

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 06:38:10 GMT

@anil: yes like this, but different color for each year and selected year.

@pooja : size of my app( qvw) is 137mb. I will create a separate QVW with inlines and I will share it.

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Thu, 08 Dec 2016 06:44:54 GMT

Do you like this, If so i will explain you clearly

For 2016 Only --

For 2015 Only --

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 06:57:09 GMT

Its good. can you use these colors? show current year(ie 2016) -rgb(248,237,236),

(year-1) - rgb(226,242,246), (year-2) - rgb(240,238,228)

And use the same color in the dimension - year also.

can you explain me what you did?

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 08:21:25 GMT

anil can you explain your expressions?

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 08:42:27 GMT

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

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Thu, 08 Dec 2016 09:28:10 GMT

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

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 10:31:14 GMT

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

Re: Display background color in Pivot table on selecting a year

Thu, 08 Dec 2016 14:25:23 GMT

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.

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Sun, 11 Dec 2016 02:44:14 GMT

NP, We will help you

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

Re: Display background color in Pivot table on selecting a year

Sun, 11 Dec 2016 10:47:59 GMT

Please check the attached qvw.

Re: Display background color in Pivot table on selecting a year

Anil_Babu_Samineni — Sun, 11 Dec 2016 13:07:08 GMT

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

Re: Display background color in Pivot table on selecting a year

sunny_talwar — Sun, 11 Dec 2016 13:25:10 GMT

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

Re: Display background color in Pivot table on selecting a year

Sun, 11 Dec 2016 16:48:11 GMT

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