Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Display background color in Pivot table on selecting a year

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

before.png

After selecting the year 2016

after.png

1 Solution

Accepted Solutions

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

Is this what you want?

When 2016 is selected

Capture.PNG

When 2015 is selected

Capture.PNG

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

20 Replies

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

Would you provide sample to work on it same as you

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

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

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

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


OR


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

2014.PNG

For 2015 --

2015.PNG

For 2016 --

2016.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

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

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.

2015.png

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

Like this?

For 2016 Only --


Capture.PNG


For 2015 Only --


Capture1.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
singhpooja
Contributor III

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

Hi Ajay,

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

Thanks,

Not applicable

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

@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

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

For 2016 Only --

Capture.PNG

For 2015 Only --

Capture1.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

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

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?